[
https://issues.apache.org/jira/browse/TORQUE-290?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Thomas Fox resolved TORQUE-290.
-------------------------------
Resolution: Fixed
Fix Version/s: 4.1
A Mixture between Strategy 1 and 2 was used.
A special element for the subselect was inserted in the generated query which
can look at the outer query. At the time the inner query is rendered, the outer
query already has its FROM clause ready, and so one can remove the duplicate
tables from the subselect's FROM clause when rendering
> 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
> Fix For: 4.1
>
>
> 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
