Doctrine 1

I am using symfony 1.4

Details

  • Type: Bug Bug
  • Status: Reopened 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

Ascending order - Click to sort in descending order
Hide
Permalink
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
Permalink
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
Permalink
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
Vote (0)
Watch (0)

Dates

  • Created:
    Updated: