[jira] [Commented] (TORQUE-290) Improve building from clause in subselect

["Thomas Fox (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TORQUE-290?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14184484#comment-14184484 ] 

Thomas Fox commented on TORQUE-290:
-----------------------------------

There are two strategies to address the problem:
1) remove the unwanted table in the inner from clause when composing the original query
2) remove the unwanted table in the inner from clause after compoing the original query

1) has the problem that the from clause of the outer query has to be known when creating the inner query. Currently, the query creation is from the inner query to the outer query. This would change lots of the query creation code and would improve its comlexity significantly.
2) is also problematic because the inner query is created to a string quite early, and all status information is lost.
The information flow when creating the query is as follows
In the case that the subselect is in the where clause:
The information starts as FromElement in the Criteria.topLevelCriterion 
then gets converted into a PreparedStatementPart in org.apache.torque.sql.objectbuilder.ObjectOrColumnPsPartBuilder.buildPs(Object, boolean, Adapter)
then gets appended to Query.whereClause (StringBuilder) in org.apache.torque.sql.SqlBuilder.appendCriterion(Criterion, Criteria, StringBuilder, Query)

So one would have to adjust FromElement, PreparedStatementPart and query.whereClause to be able to postprocess the query.
This is also quite a big change.

> Improve building from clause in subselect
> -----------------------------------------
>
>                 Key: TORQUE-290
>                 URL: https://issues.apache.org/jira/browse/TORQUE-290
>             Project: Torque
>          Issue Type: Improvement
>          Components: Runtime
>    Affects Versions: 4.0
>            Reporter: Thomas Fox
>            Assignee: Thomas Fox
>              Labels: incompatibleChange
>
> Currently, it is not easily possible to reference columns in the main select from a subselect.
> For example, to select authors which have published exactly one book, one can use the SQL:
> SELECT * FROM author WHERE (SELECT COUNT(*) FROM book WHERE book.author_id=author.author_id)=1
> One would like to achieve this by
> Criteria subselect = new Criteria();
> subselect.where(BookPeer.AUTHOR_ID, AuthorPeer.AUTHOR_ID);
> subselect.addSelectColumn(new Count("*"));
> Criteria criteria = new Criteria();
> criteria.where(subselect, 1);
> List<Author> result = AuthorPeer.doSelect(criteria);
> but the SQL results in (note the additional author in the from clause of the subselect)
> SELECT * FROM author WHERE (SELECT COUNT(*) FROM book, author WHERE book.author_id=author.author_id)=1
> So the desired behavior is:
> If a table also exists in the from clause of the outer criteria, it should not be added to the from clause of the subselect
> NOTE1: This change can change the behaviour of Torque in existing code
> NOTE2: Workarounds exist, e.g. defining the FROM clause of the subselect manually using Criteria.addFrom() or using "new ColumnImpl(null,null,null,AuthorPeer.AUTHOR_ID.getSqlExpression())" as join column in the WHERE clause of the subselect.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org