[
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-unsubscr...@db.apache.org
For additional commands, e-mail: torque-dev-h...@db.apache.org
