Uploaded image for project: 'Torque'
  1. Torque
  2. TORQUE-290

Improve building from clause in subselect

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 4.0
    • Fix Version/s: 5.0
    • Component/s: Runtime

      Description

      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.

        Attachments

          Activity

            People

            • Assignee:
              tfischer Thomas Fox
              Reporter:
              tfischer Thomas Fox
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: