view-entity view-link for multiple aliases

[Foo Shyn Chung]

Hi all,

I have a view created via the DynamicViewEntity in a groovy script. This
view of mine consist of few tables and another view which is declared in a
entitymodel.xml in another module.

In one of the join, the link need to consist of relationship from 3 tables,
i.e.

select * from ((tableA a inner join tableB b on a.first_id = b.first_id )
inner join tableC c where a.one_id = c.one_id and b.two_id = c.two_id)

Note that for the view link for the second inner join, i need to have alias
for both tableA and tableB. I'd searched for a while and it seems that the
workaround is by using entity-condition but in DynamicViewEntity there's no
method for me to add that.

Anyone has come across this issue before? Any advice or solution?

Thanx
FooShyn

[Foo Shyn Chung]

On second thought, is there anything similar to the JdbcTemplate in Spring
framework that let you run customized SQL directly on OFBiz?

Thanx
FooShyn

On Wed, Apr 11, 2012 at 10:56 PM, Foo Shyn Chung <[hidden email]> wrote:

> Hi all,
>
> I have a view created via the DynamicViewEntity in a groovy script. This
> view of mine consist of few tables and another view which is declared in a
> entitymodel.xml in another module.
>
> In one of the join, the link need to consist of relationship from 3
> tables, i.e.
>
> select * from ((tableA a inner join tableB b on a.first_id = b.first_id )
> inner join tableC c where a.one_id = c.one_id and b.two_id = c.two_id)
>
> Note that for the view link for the second inner join, i need to have
> alias for both tableA and tableB. I'd searched for a while and it seems
> that the workaround is by using entity-condition but in DynamicViewEntity
> there's no method for me to add that.
>
> Anyone has come across this issue before? Any advice or solution?
>
> Thanx
> FooShyn
>

[Jacques Le Roux]

You have then to use directly JDBC. Look for prepareStatement in java code for examples.
SQLProcessor.executeQuery(String sql) is certainly the best match

Jacques

From: "Foo Shyn Chung" <[hidden email]>

> On second thought, is there anything similar to the JdbcTemplate in Spring
> framework that let you run customized SQL directly on OFBiz?
>
> Thanx
> FooShyn
>
> On Wed, Apr 11, 2012 at 10:56 PM, Foo Shyn Chung <[hidden email]> wrote:
>
>> Hi all,
>>
>> I have a view created via the DynamicViewEntity in a groovy script. This
>> view of mine consist of few tables and another view which is declared in a
>> entitymodel.xml in another module.
>>
>> In one of the join, the link need to consist of relationship from 3
>> tables, i.e.
>>
>> select * from ((tableA a inner join tableB b on a.first_id = b.first_id )
>> inner join tableC c where a.one_id = c.one_id and b.two_id = c.two_id)
>>
>> Note that for the view link for the second inner join, i need to have
>> alias for both tableA and tableB. I'd searched for a while and it seems
>> that the workaround is by using entity-condition but in DynamicViewEntity
>> there's no method for me to add that.
>>
>> Anyone has come across this issue before? Any advice or solution?
>>
>> Thanx
>> FooShyn
>>
>

[Foo Shyn Chung]

I found that the usage are quite limited. If i need to populate the result
on a ftl form, will i be able to utilized the existing code structure, i.e.
list sales as sale, sale.id to get the value of the id selected?

Or is there anyway to provide a view-link that joins 2 different tables as
i mentioned above?

Thanx
FooShyn

On Fri, Apr 13, 2012 at 12:36 AM, Jacques Le Roux <
[hidden email]> wrote:

> You have then to use directly JDBC. Look for prepareStatement in java code
> for examples.
> SQLProcessor.executeQuery(**String sql) is certainly the best match
>
> Jacques
>
> From: "Foo Shyn Chung" <[hidden email]>
>
>  On second thought, is there anything similar to the JdbcTemplate in Spring
>> framework that let you run customized SQL directly on OFBiz?
>>
>> Thanx
>> FooShyn
>>
>> On Wed, Apr 11, 2012 at 10:56 PM, Foo Shyn Chung <[hidden email]>
>> wrote:
>>
>>  Hi all,
>>>
>>> I have a view created via the DynamicViewEntity in a groovy script. This
>>> view of mine consist of few tables and another view which is declared in
>>> a
>>> entitymodel.xml in another module.
>>>
>>> In one of the join, the link need to consist of relationship from 3
>>> tables, i.e.
>>>
>>> select * from ((tableA a inner join tableB b on a.first_id = b.first_id )
>>> inner join tableC c where a.one_id = c.one_id and b.two_id = c.two_id)
>>>
>>> Note that for the view link for the second inner join, i need to have
>>> alias for both tableA and tableB. I'd searched for a while and it seems
>>> that the workaround is by using entity-condition but in DynamicViewEntity
>>> there's no method for me to add that.
>>>
>>> Anyone has come across this issue before? Any advice or solution?
>>>
>>> Thanx
>>> FooShyn
>>>
>>>
>>