view-entity subquery WHERE clauses supported?

[Christian Carlow-OFBizzer]

Does anyone know if it is possible to constrain joined view-entity
subqueries with their own WHERE clauses?

For example:

select * from tbl1 left join (select * from tbl2 where tbl2.col1 =
'someVal') tbl3 on tbl1.col1 = tbl3.col1

[Christian Carlow-OFBizzer]

 From the debugging I've done so far, this functionality does not seem
to be supported yet.

The subquery/subselect is added to the sql query string with this line
in GenericDAO.java:
     sqlBuffer.append(SqlJdbcUtil.makeFromClause(modelEntity,
datasourceInfo));

Which in turn calls makeViewTable in GenericDAO.java:
     restOfStatement.append(makeViewTable(linkEntity, datasourceInfo));

Which in turn calls makeViewWhereClause in SqlJdbcUtil.java:
     String viewWhereClause = makeViewWhereClause(modelEntity,
datasourceInfo.joinStyle);

The makeViewWhereClause function in SqlJdbcUtil.java performs the
following action which prevents the WHERE clause from being added:
     if ("ansi".equals(joinStyle) ||
"ansi-no-parenthesis".equals(joinStyle)) {
                 // nothing to do here, all done in the JOIN clauses
     }
     return "";

In order for subqueries to contain where clauses within them, all of the
methods listed would need to be revised to pass the GenericDAO object
from makeFromClause up to the makeViewWhereClause because the GenericDAO
object is what is responsible for building the where clauses which it
proceeds to do immediately after the makeFromClause is finished
executing with the makeConditionWhereString function.

If the methods were revised to allow for subquery where clauses then the
makeconditionWhereString function in GenericDAO.java would need to be
changed to exclude the subqueries where clauses in the containing
query's where clause:
     makeConditionWhereString(sqlBuffer, " WHERE ", modelEntity,
whereEntityCondition, viewWhereConditions, whereEntityConditionParams);

It seems that subquery where clauses could be excluded from the
containing query where clause list by commenting out the following lines
in the makeConditionWhereString method:
         String viewClause =
SqlJdbcUtil.makeViewWhereClause(modelEntity, datasourceInfo.joinStyle);
         if (viewClause.length() > 0) {
conditions.add(EntityCondition.makeConditionWhere(viewClause));
         }

Does anyone see a problem with changing the subquery (view-entity
view-entities) functionality to work this way?


On 09/16/2013 10:57 AM, Christian Carlow wrote:
> Does anyone know if it is possible to constrain joined view-entity
> subqueries with their own WHERE clauses?
>
> For example:
>
> select * from tbl1 left join (select * from tbl2 where tbl2.col1 =
> 'someVal') tbl3 on tbl1.col1 = tbl3.col1

[Christian Carlow-OFBizzer]

For a little perspective,

Essentially what I'm trying to accomplish is a simplified shipping
schedule list based on order item ship group due quantities.

I created a view-entity that joins together orderHeader, orderItem,
orderItemShipGroup, and orderItemshipGroupAssoc.

Additionally I created a view entity that joins together itemIssuance
and Shipment and joined this view-entity twice to the previous view
entity mentioned but created one with a condition where
Shipment.statusId = "SHIPMENT_SHIPPED" and the other with a condition
where Shipment.statusId = "SHIPMENT_INPUT".

I'm trying to get the total amount issued with sum(II.quantity -
II.cancelQuantity), which references the second join where
Shipment.statusId = "SHIPMENT_INPUT"

and the total amount not shipped with MIN(OISGA.quantity) -
sum(II.quantity - II.cancelQuantity) which references the first join
where Shipment.statusId = "SHIPMENT_SHIPPED"

On 09/16/2013 10:57 AM, Christian Carlow wrote:
> Does anyone know if it is possible to constrain joined view-entity
> subqueries with their own WHERE clauses?
>
> For example:
>
> select * from tbl1 left join (select * from tbl2 where tbl2.col1 =
> 'someVal') tbl3 on tbl1.col1 = tbl3.col1

[Christian Carlow-OFBizzer]

Conditional joins would also be solution to my problem.  I tried to add
an entity-condition within the view-entity but it was output as a WHERE
clause.

Was the issue mentioned here ever resolved?

http://ofbiz.135035.n4.nabble.com/View-Link-Entity-Conditions-td4219210.html

On 09/16/2013 03:16 PM, Christian Carlow wrote:

> For a little perspective,
>
> Essentially what I'm trying to accomplish is a simplified shipping
> schedule list based on order item ship group due quantities.
>
> I created a view-entity that joins together orderHeader, orderItem,
> orderItemShipGroup, and orderItemshipGroupAssoc.
>
> Additionally I created a view entity that joins together itemIssuance
> and Shipment and joined this view-entity twice to the previous view
> entity mentioned but created one with a condition where
> Shipment.statusId = "SHIPMENT_SHIPPED" and the other with a condition
> where Shipment.statusId = "SHIPMENT_INPUT".
>
> I'm trying to get the total amount issued with sum(II.quantity -
> II.cancelQuantity), which references the second join where
> Shipment.statusId = "SHIPMENT_INPUT"
>
> and the total amount not shipped with MIN(OISGA.quantity) -
> sum(II.quantity - II.cancelQuantity) which references the first join
> where Shipment.statusId = "SHIPMENT_SHIPPED"
>
> On 09/16/2013 10:57 AM, Christian Carlow wrote:
>> Does anyone know if it is possible to constrain joined view-entity
>> subqueries with their own WHERE clauses?
>>
>> For example:
>>
>> select * from tbl1 left join (select * from tbl2 where tbl2.col1 =
>> 'someVal') tbl3 on tbl1.col1 = tbl3.col1
>

[Christian Carlow-OFBizzer]

Watching this https://issues.apache.org/jira/browse/OFBIZ-4781 and will
probably start working on it if no one has solved it yet.  I need to
check the branch to see if it was ever solved there.

On 09/16/2013 03:56 PM, Christian Carlow wrote:

> Conditional joins would also be solution to my problem.  I tried to
> add an entity-condition within the view-entity but it was output as a
> WHERE clause.
>
> Was the issue mentioned here ever resolved?
>
> http://ofbiz.135035.n4.nabble.com/View-Link-Entity-Conditions-td4219210.html 
>
>
> On 09/16/2013 03:16 PM, Christian Carlow wrote:
>> For a little perspective,
>>
>> Essentially what I'm trying to accomplish is a simplified shipping
>> schedule list based on order item ship group due quantities.
>>
>> I created a view-entity that joins together orderHeader, orderItem,
>> orderItemShipGroup, and orderItemshipGroupAssoc.
>>
>> Additionally I created a view entity that joins together itemIssuance
>> and Shipment and joined this view-entity twice to the previous view
>> entity mentioned but created one with a condition where
>> Shipment.statusId = "SHIPMENT_SHIPPED" and the other with a condition
>> where Shipment.statusId = "SHIPMENT_INPUT".
>>
>> I'm trying to get the total amount issued with sum(II.quantity -
>> II.cancelQuantity), which references the second join where
>> Shipment.statusId = "SHIPMENT_INPUT"
>>
>> and the total amount not shipped with MIN(OISGA.quantity) -
>> sum(II.quantity - II.cancelQuantity) which references the first join
>> where Shipment.statusId = "SHIPMENT_SHIPPED"
>>
>> On 09/16/2013 10:57 AM, Christian Carlow wrote:
>>> Does anyone know if it is possible to constrain joined view-entity
>>> subqueries with their own WHERE clauses?
>>>
>>> For example:
>>>
>>> select * from tbl1 left join (select * from tbl2 where tbl2.col1 =
>>> 'someVal') tbl3 on tbl1.col1 = tbl3.col1
>>
>

[Christian Carlow-OFBizzer]

The conditional join functionality is supported in the 12.04 release so
I'm switching to it rather than attempting to implement in 11.04.  The
conditional join functionality essentially accomplishes the same thing
that a subquery WHERE clause would.

On 09/16/2013 10:57 AM, Christian Carlow wrote:
> Does anyone know if it is possible to constrain joined view-entity
> subqueries with their own WHERE clauses?
>
> For example:
>
> select * from tbl1 left join (select * from tbl2 where tbl2.col1 =
> 'someVal') tbl3 on tbl1.col1 = tbl3.col1