Order by group in Doctrine 2

Question

I am using Symfony 2 PR12 with Doctrine 2 and MySQL. I have a database that stores articles and presentations of these articles:

// ...
class Article {

    /**
     * @orm:Column(type="bigint")
     * @orm:Id
     * @orm:GeneratedValue
     * @var int
     */
    protected $id;

    /**
     * @orm:OneToMany(targetEntity="ArticleView",mappedBy="article")
     * @var ArrayCollection
     */
    protected $views;

    // ...
}

// ...
class ArticleView {

    /**
     * @orm:Column(type="bigint")
     * @orm:Id
     * @orm:GeneratedValue
     * @var int
     */
    protected $id;

    /**
     * @orm:Column(type="bigint",name="DateRead",nullable=true)
     * @var int
     */
    protected $viewDate;

    /**
     * @orm:ManyToOne(targetEntity="Article",inversedBy="views")
     * @var Article
     */
    protected $article;

    // ...
}

I want to get, for example, the 20 most recently viewed articles. My first thought would be this:

$qb = <instance of Doctrine\ORM\QueryBuilder>;
$qb->select('a')
   ->from('Article', 'a')
   ->join('a.views', 'v')
   ->orderBy('v.viewDate', 'DESC')
   ->groupBy('a.id')
   ->setMaxResults(20)
;

However, if there is more than one view associated with the article, the order-by / group-by combination gives unpredictable results for ordering.

MySQL, , http://www.artfulsoftware.com/infotree/mysqlquerytree.php ( → ). , DQL, , , .

, ?

Answer 1

:

$qb
    ->select('a')
    ->from('Article', 'a')
    ->join('a.views', 'v')
    ->orderBy('v.viewDate', 'DESC')
    ->setMaxResults(20)

    // Only select the most recent article view for each individual article
    ->where('v.viewDate = (SELECT MAX(v2.viewDate) FROM ArticleView v2 WHERE v2.article = a)')

, ArticleView, . , SQL- - - :).