Doctrine_Pager returns wrong results with multiple groupBy columns
I have Doctrine query setup with mySQL as database:
$q = Doctrine_Query::create(); $q->select('make.id, make.make, model.id, model.year, model.model') ->from('Make make') ->innerJoin('make.Models model'); $q->groupBy('make.id, model.id'); // <-- this is where the problem $q->orderBy('make ASC'); $q->setHydrationMode(Doctrine_Core::HYDRATE_SCALAR); $pager = new Doctrine_Pager($q, 1, 1); // just want one result to prove the concept $items = $pager->execute();
This executes this three queries (from mysql log):
SELECT COUNT(*) AS
num_results
FROM (SELECTm
.id
FROMmake
m
INNER JOINmodel
m2
ONm
.id
=m2
.make_id
GROUP BYm
.id
,m2
.id
)dctrn_count_query
SELECT DISTINCT
m3
.id
FROMmake
m3
INNER JOINmodel
m4
ONm3
.id
=m4
.make_id
GROUP BYm3
.id
,m4
.id
ORDER BYm3
.make
ASC LIMIT 1SELECT
m
.id
ASm__id
,m
.make
ASm__make
,m2
.id
ASm2__id
,m2
.year
ASm2__year
,m2
.model
ASm2__model
FROMmake
m
INNER JOINmodel
m2
ONm
.id
=m2
.make_id
WHEREm
.id
IN ('33') GROUP BYm
.id
,m2
.id
ORDER BYm
.make
ASC
And the result set is
array
0 =>
array
'make_id' => string '33' (length=2)
'make_make' => string 'Alfa-romeo' (length=10)
'model_id' => string '288' (lengt开发者_Go百科h=3)
'model_year' => string '2010' (length=4)
'model_model' => string '159' (length=3)
1 =>
array
'make_id' => string '33' (length=2)
'make_make' => string 'Alfa-romeo' (length=10)
'model_id' => string '289' (length=3)
'model_year' => string '2010' (length=4)
'model_model' => string 'MiTo' (length=4)
2 =>
array
'make_id' => string '33' (length=2)
'make_make' => string 'Alfa-romeo' (length=10)
'model_id' => string '290' (length=3)
'model_year' => string '2010' (length=4)
'model_model' => string '159 SPORTWAGON' (length=14)
The problem is with second query that returns make.id which is used in third query to select 1 make (Alfa-Romeo which has 3 models). What I want is to return NUMBER OF make/model combinations.
If I change number of items returned in Doctrine_Pager to 2, I get 33 rows (because from two selected makes Alfa-Romeo has 3 models and Audi (which is next) has 30 models.
Where I'm making a mistake?
You can not combine multiple columns in a single group by. Try this...
$q->groupBy('make.id');
$q->addGroupBy('model.id');
or a little more compressed...
$q->groupBy('make.id')->addGroupBy('model.id');
$table->setAttribute(Doctrine_Core::ATTR_QUERY_LIMIT, Doctrine_Core::LIMIT_ROWS);
http://www.doctrine-project.org/projects/orm/1.2/docs/manual/dql-doctrine-query-language/en
精彩评论