学说加入MAX（）

I have Entities

Template {

   /**
     * @ORM\OneToMany(targetEntity="TemplateSnapshot",  mappedBy="template", cascade={"all"})
     */
    protected $snapshots;

}


/**
 * @ORM\Entity
 */
class TemplateSnapshot {

    /**
     * @ORM\Column(type="datetime")
     * @var \DateTime
     */
    protected $modTime;

}

And try get Template ordered by last modTime in TemplateSnapshot

QueryBuilder $query
                    ->leftJoin('EditorBundle:TemplateSnapshot','s','WITH', $tableAlias.'.id = s.template_id')
                    ->groupBy($tableAlias.'.id')
                    ->orderBy('s.modTime','desc')
            ;

i get

SELECT t0_.id AS id0, t0_.location AS location1, t0_.name AS name2, t1_.modTime AS modTime3 FROM Template t0_ LEFT JOIN TemplateSnapshot t1_ ON t0_.id = t1_.template_id LEFT JOIN TemplateSnapshot t2_ ON (t0_.id = t2_.template_id) WHERE t0_.name LIKE ? GROUP BY t0_.id ORDER BY t2_.modTime DESC LIMIT 20

this return first join row. (first group then sort). I want get join with newest TemplateSnapshot

other try

$query

                ->leftJoin('TemplateEditorBundle:TemplateSnapshot',
                    's',
                    'WITH',
                    $tableAlias.'.id = s.template_id and s.modTime = MAX(s.modTime)'
                )
                ->groupBy($tableAlias.'.id')
                ->orderBy('s.modTime','desc')
            ;

this i get :

SELECT t0_.id AS id0, t0_.location AS location1, t0_.name AS name2, t1_.modTime AS modTime3, MAX(t2_.modTime) AS sclr4 FROM Template t0_ LEFT JOIN TemplateSnapshot t1_ ON t0_.id = t1_.template_id LEFT JOIN TemplateSnapshot t2_ ON (t0_.id = t2_.template_id AND t2_.modTime = MAX(t2_.modTime)) WHERE t0_.name LIKE ? ORDER BY t2_.modTime DESC LIMIT 20

and error

enter code hereInvalid use of group function

other idea

i try direct query to TemplateSnapshot

$source = new Entity('TemplateEditorBundle:TemplateSnapshot');

 $query
                   ->orderBy($tableAlias.'.modTime','desc') ->groupBy($tableAlias.'.template_id')
            ;

but it first gorup then order so dont get newest.

You need to implement Native query for this purpose. Query should look like:

SELECT * FROM TemplateSnapshot ts
INNER JOIN
(SELECT MAX(mod_time) AS mod_time, template_id FROM TemplateSnapshot GROUP BY template_id) AS ts_max
ON ts.template_id = ts_max.template_id AND ts.mod_time = ts_max.mod_time

This query will return you only rows with maximum value of mod_time from TemplateSnapshot for every Template.