Details

    • Type: Bug Bug
    • Status: Reopened
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:
      Symfony 1.4

      Description

      $q1 = Doctrine_Query::create()
      ->select('m.questionaire, COUNT(f.id) AS bref')
      ->from('machine m')
      ->leftJoin('m.relations r ON r.machine_id=m.id')
      ->leftJoin('r.ref f ON r.ref_id=f.id AND f.part_number LIKE "B%"')
      ->groupBy('m.questionaire')
      ;
      $r1 = $q1->execute();
      $q1q = $q1->getSqlQuery();

      $q2 = Doctrine_Query::create()
      ->select('m.questionaire, COUNT(f.id) AS nonbref')
      ->from('machine m')
      ->leftJoin('m.relations r ON r.machine_id=m.id')
      ->leftJoin('r.ref f ON r.ref_id=f.id AND f.part_number NOT LIKE "B%"')
      ->groupBy('m.questionaire')
      ;
      $r2 = $q2->execute();
      $q2q = $q2->getSqlQuery();

      $this->reports->setQuery(Doctrine_Query::create()
      ->select('')
      ->from('(SQL:'.$q1q.'} q1')
      ->leftJoin('(SQL:'.$q2q.') q2 ON q1.questionaire=q2questionaire')
      );
      echo $this->reports->getQuery()->getSqlQuery(); die;

      This outputs Couldn't find class (SQL

      How to use in such a case ?

        Activity

        Hide
        Jonathan H. Wage added a comment -

        What is the SQL: syntax you are using here? That is definitely not something that is "supported"

        Show
        Jonathan H. Wage added a comment - What is the SQL: syntax you are using here? That is definitely not something that is "supported"
        Hide
        Petronel MALUTAN added a comment - - edited

        Dear Jonathan

        The example I've tried is based on the :
        http://www.symfony-project.org/cookbook/1_2/en/retrieving_data_with_doctrine
        Please find Sub-Queries in this article and see what I mean.

        My problem started from a SQL query created in phpmyadmin, I've tried to convert to doctrine. Following is the mysql query which nicely work:

        select * from (select m1.questionaire, COUNT(f1.id) AS n1_refs
        from machine AS m1
        left join relation AS r1 on m1.id=r1.machine_id
        left join ref AS f1 on f1.id=r1.ref_id and f1.part_number LIKE 'B%'
        group by m1.questionaire) as a1

        left join

        (select m2.questionaire, COUNT(f2.id) AS n2_refs
        from machine AS m2
        left join relation AS r2 on m2.id=r2.machine_id
        left join ref AS f2 on f2.id=r2.ref_id and f2.part_number not LIKE 'B%'
        group by m2.questionaire) as a2
        on a1.questionaire=a2.questionaire

        and my trying was to create 2 easier DQL queries and than join them:

        $this->q = Doctrine_Query::create()
        ->select('')
        ;

        $this->q1 = $this->q->createSubquery()
        ->select('m.questionaire, m.aszero, COUNT(f.id) AS bref')
        ->from('machine m')
        ->leftJoin('m.relations r ON r.machine_id=m.id')
        ->leftJoin('r.ref f ON f.id=r.ref_id AND f.part_number LIKE "B%"')
        ->groupBy('m.questionaire')
        ;
        //$r1 = $q1->fetchArray();
        //$this->r1=$this->q1->execute(array(),Doctrine_Core::HYDRATE_RECORD);

        $this->q2 = $this->q->createSubquery()
        ->select('m.questionaire, m.aszero, COUNT(f.id) AS nonbref')
        ->from('machine m')
        ->leftJoin('m.relations r ON r.machine_id=m.id')
        ->leftJoin('r.ref f ON f.id=r.ref_id AND f.part_number NOT LIKE "B%"')
        ->groupBy('m.questionaire')
        ;
        //$this->r2=$this->q2->execute(array(),Doctrine_Core::HYDRATE_RECORD);

        $this->q->from($this->q1->getDql() . ' q1)')
        >leftJoin($this>q2->getDql() . ' q2 ON q1.questionaire=q2questionaire');

        echo $this->q->getSqlQuery(); die;

        This is outputting :

        500 | Internal Server Error | Doctrine_Exception
        Couldn't find class SELECT

        so please tell me is it now more clear and make some sense ?
        How to make it work ?

        With kind regards

        Petronel

        Show
        Petronel MALUTAN added a comment - - edited Dear Jonathan The example I've tried is based on the : http://www.symfony-project.org/cookbook/1_2/en/retrieving_data_with_doctrine Please find Sub-Queries in this article and see what I mean. My problem started from a SQL query created in phpmyadmin, I've tried to convert to doctrine. Following is the mysql query which nicely work: select * from (select m1.questionaire, COUNT(f1.id) AS n1_refs from machine AS m1 left join relation AS r1 on m1.id=r1.machine_id left join ref AS f1 on f1.id=r1.ref_id and f1.part_number LIKE 'B%' group by m1.questionaire) as a1 left join (select m2.questionaire, COUNT(f2.id) AS n2_refs from machine AS m2 left join relation AS r2 on m2.id=r2.machine_id left join ref AS f2 on f2.id=r2.ref_id and f2.part_number not LIKE 'B%' group by m2.questionaire) as a2 on a1.questionaire=a2.questionaire and my trying was to create 2 easier DQL queries and than join them: $this->q = Doctrine_Query::create() ->select('') ; $this->q1 = $this->q->createSubquery() ->select('m.questionaire, m.aszero, COUNT(f.id) AS bref') ->from('machine m') ->leftJoin('m.relations r ON r.machine_id=m.id') ->leftJoin('r.ref f ON f.id=r.ref_id AND f.part_number LIKE "B%"') ->groupBy('m.questionaire') ; //$r1 = $q1->fetchArray(); //$this->r1=$this->q1->execute(array(),Doctrine_Core::HYDRATE_RECORD); $this->q2 = $this->q->createSubquery() ->select('m.questionaire, m.aszero, COUNT(f.id) AS nonbref') ->from('machine m') ->leftJoin('m.relations r ON r.machine_id=m.id') ->leftJoin('r.ref f ON f.id=r.ref_id AND f.part_number NOT LIKE "B%"') ->groupBy('m.questionaire') ; //$this->r2=$this->q2->execute(array(),Doctrine_Core::HYDRATE_RECORD); $this->q->from($this->q1->getDql() . ' q1)') >leftJoin($this >q2->getDql() . ' q2 ON q1.questionaire=q2questionaire'); echo $this->q->getSqlQuery(); die; This is outputting : 500 | Internal Server Error | Doctrine_Exception Couldn't find class SELECT so please tell me is it now more clear and make some sense ? How to make it work ? With kind regards Petronel
        Hide
        Jonathan H. Wage added a comment -

        Can you make a test case that I can run on my machine to see the problem?

        Show
        Jonathan H. Wage added a comment - Can you make a test case that I can run on my machine to see the problem?

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Petronel MALUTAN
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: