Re: view-entity subquery WHERE clauses supported?

Posted by Christian Carlow-OFBizzer on
URL: http://ofbiz.116.s1.nabble.com/view-entity-subquery-WHERE-clauses-supported-tp4643984p4643988.html

 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