[ https://issues.apache.org/jira/browse/TORQUE-290?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Thomas Fox updated TORQUE-290: ------------------------------ Labels: incompatibleChange (was: ) > 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: > Select authors which have published more than three books > 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.Currently, it is not easily > possible to reference columns in the main select from a subselect. > For examle -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira --------------------------------------------------------------------- To unsubscribe, e-mail: torque-dev-unsubscr...@db.apache.org For additional commands, e-mail: torque-dev-h...@db.apache.org