开发者

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 (SELECT m.id FROM make m INNER JOIN model m2 ON m.id = m2.make_id GROUP BY m.id, m2.id) dctrn_count_query

SELECT DISTINCT m3.id FROM make m3 INNER JOIN model m4 ON m3.id = m4.make_id GROUP BY m3.id, m4.id ORDER BY m3.make ASC LIMIT 1

SELECT m.id AS m__id, m.make AS m__make, m2.id AS m2__id, m2.year AS m2__year, m2.model AS m2__model FROM make m INNER JOIN model m2 ON m.id = m2.make_id WHERE m.id IN ('33') GROUP BY m.id, m2.id ORDER BY m.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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜