Doctrine 1
  1. Doctrine 1
  2. DC-692

Can not create a subquery in where

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.2
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None
    • Environment:
      XP Xamp

      Description

      Hi All

      I can not figure out how to make a subquery work in doctrine. I get the subquery to look fine in DQL but when doctrine converts the DQL to SQL the subquery is automatically removed.

      Here is an example:
      This PHP:

      $q = Doctrine_Query::create(); 
      $q->from('Customer Customer'); 
      $q->addWhere(' Customer.id in (SELECT Customer.id as customer_id FROM Customer Customer)'); 
      $q->addSelect('Customer.id'); 
      $q->addSelect('Customer.id as customer_id');
      $q->limit(20); 
      

      Creates this DQL:

      SELECT Customer.id, Customer.id as customer_id FROM Customer Customer WHERE Customer.id in (SELECT Customer.id as customer_id FROM Customer Customer) LIMIT 20
      

      Which creates this broken SQL:

      SELECT p.id AS p__id, p.id AS p__0 FROM product_customers p WHERE (p.id in ()) LIMIT 20
      

      Notice that the subquery has been replaced with just "()".

      Is there something wrong with my execution, does doctrine just not support subqueries, or is there a bug here?

      Thanks much in advance.

      Sincerely

      Will Ferrer

        Activity

        Hide
        will ferrer added a comment -

        I have found a way around my problem (looked through the code to figure out how it worked) – seems that if I put 'SQL:' in front of my sub query, and use SQL instead of DQL in the sub query like so:

        $q = Doctrine_Query::create(); 
        $q->from('Customer Customer'); 
        $q->addWhere(' Customer.id in (SQL:SELECT p.id AS p__0 FROM product_customers p)'); 
        $q->addSelect('Customer.id'); 
        $q->addSelect('Customer.id as customer_id'); 
        

        The correct sql is made:

        SELECT p.id AS p__id, p.id AS p__0 FROM product_customers p WHERE (p.id in (SELECT p.id AS p__0 FROM product_customers p)) LIMIT 20
        

        I am not still not sure what is up with trying to use the DQL instead. Looking through the code I see that on line 842 of Doctrine_Query my DQL subquery was being passed to the function $this->createSubquery()->parseDqlQuery($trimmed) like so:

         $q = $this->createSubquery()->parseDqlQuery($trimmed);
        $trimmed = $q->getSqlQuery();
        

        $trimmed was coming back as false here which is why my subquery wasn't working.

        Hope that is helpful.

        Best Regards

        Will Ferrer

        Show
        will ferrer added a comment - I have found a way around my problem (looked through the code to figure out how it worked) – seems that if I put 'SQL:' in front of my sub query, and use SQL instead of DQL in the sub query like so: $q = Doctrine_Query::create(); $q->from('Customer Customer'); $q->addWhere(' Customer.id in (SQL:SELECT p.id AS p__0 FROM product_customers p)'); $q->addSelect('Customer.id'); $q->addSelect('Customer.id as customer_id'); The correct sql is made: SELECT p.id AS p__id, p.id AS p__0 FROM product_customers p WHERE (p.id in (SELECT p.id AS p__0 FROM product_customers p)) LIMIT 20 I am not still not sure what is up with trying to use the DQL instead. Looking through the code I see that on line 842 of Doctrine_Query my DQL subquery was being passed to the function $this->createSubquery()->parseDqlQuery($trimmed) like so: $q = $ this ->createSubquery()->parseDqlQuery($trimmed); $trimmed = $q->getSqlQuery(); $trimmed was coming back as false here which is why my subquery wasn't working. Hope that is helpful. Best Regards Will Ferrer

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            will ferrer
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: