php - Doctrine_Pager 返回带有多个 groupBy 列的错误结果

Question

我使用 mySQL 作为数据库设置了 Doctrine 查询:

  $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();

这会执行这三个查询(来自 mysql 日志):

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

结果集是

    array
        0 => 
            array
              'make_id' => string '33' (length=2)
              'make_make' => string 'Alfa-romeo' (length=10)
              'model_id' => string '288' (length=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)

问题在于第二个查询返回 make.id，它在第三个查询中用于选择 1 个品牌(Alfa-Romeo，它有 3 个模型)。我想要的是返回 NUMBER OF 品牌/型号组合。

如果我将 Doctrine_Pager 中返回的项目数更改为 2，我会得到 33 行(因为从两个选定的品牌中，Alfa-Romeo 有 3 个模型，而 Audi(下一个)有 30 个模型。

我哪里出错了？

Answer 1

您不能在单个分组依据中组合多个列。试试这个...

$q->groupBy('make.id');
$q->addGroupBy('model.id');

或者稍微压缩一点...

$q->groupBy('make.id')->addGroupBy('model.id');