Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2891

Impossible to pass a limit to a subquery

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.4.2
    • Fix Version/s: None
    • Component/s: None
    • Security Level: All
    • Labels:
      None

      Description

      It seems that passing the limit to a subquery is not working

       
      $subquery = $em->createQueryBuilder()->from('...')->where('...')->setMaxResults(5);
      $query = $em->createQueryBuilder()->from('...')->where(
         $qb->expr()->in('p.id', $subquery->getDQL())
      );
      $query->getQuery()->getResult();
      

      The query works but the is no specified limit in the resulting SQL.
      I am aware that DQL does not support the limits and offsets, so i guess there should be another way to get this working?

        Activity

        Hide
        alex added a comment - - edited

        I think if you try to build any query with QueryBuilder, set a limit to it with setMaxResults then call getDQL method, you should see that the output contains no info about limit.
        So if you look at my code example , at $qb->expr()>in('p.id', $subquery>getDQL()), then you will see that the getDQL passes to the IN expression a query which already DOES NOT have limit. So this is the place where any info about limits and offsets gets lost.

        So I fail to see what it has to do with any specific db engine,however I can provide the mysql resulting query if you want,though it looked perfectly normal to me,just lacks the LIMIT part.

        Show
        alex added a comment - - edited I think if you try to build any query with QueryBuilder, set a limit to it with setMaxResults then call getDQL method, you should see that the output contains no info about limit. So if you look at my code example , at $qb->expr() >in('p.id', $subquery >getDQL()), then you will see that the getDQL passes to the IN expression a query which already DOES NOT have limit. So this is the place where any info about limits and offsets gets lost. So I fail to see what it has to do with any specific db engine,however I can provide the mysql resulting query if you want,though it looked perfectly normal to me,just lacks the LIMIT part.
        Hide
        Steve Müller added a comment - - edited

        Hmmm I am not quite sure if the limit/offset is invoked for subqueries but I don't see why it shouldn't. Also I think this is not a DBAL issue because the limit/offset support for MySQL is the easiest we have on all platform. See: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/MySqlPlatform.php#L51-L63
        The query doesn't have to be modified but instead only the limit clause is appended to the query. Can you maybe provide the generated SQL for that query?

        Show
        Steve Müller added a comment - - edited Hmmm I am not quite sure if the limit/offset is invoked for subqueries but I don't see why it shouldn't. Also I think this is not a DBAL issue because the limit/offset support for MySQL is the easiest we have on all platform. See: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/MySqlPlatform.php#L51-L63 The query doesn't have to be modified but instead only the limit clause is appended to the query. Can you maybe provide the generated SQL for that query?
        Hide
        alex added a comment -

        MySql

        Show
        alex added a comment - MySql
        Hide
        Steve Müller added a comment -

        Can you please tell which database platform you are using? Because limiting results heavily depends on the platform used.

        Show
        Steve Müller added a comment - Can you please tell which database platform you are using? Because limiting results heavily depends on the platform used.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            alex
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: