db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Fox (JIRA)" <j...@apache.org>
Subject [jira] [Resolved] (TORQUE-290) Improve building from clause in subselect
Date Tue, 06 Oct 2015 03:18:26 GMT

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


Mime
View raw message