StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSymfony 2 Doctrine: OrderBy - GroupBy Max Element with Joins
text
Body
copied!<p>My problem is Doctrine and SQL GroupBy with Order By</p> <p>So a little description there is a shop(s), the shop has articles(a) and there is an entity visit counter(vc) to count the visits of different things, also used for shop and article.</p> <p>I want a list of articles(top articles), but just the most visited article for each shop. </p> <pre><code> class Shop { private $id; private $name; private $is_published; ... /** * OneToMany articles */ private $articles; } class Article { private $id; private $name; private $is_published; ... /* ManyToOne Shop*/ private $shop; } class VisitCounter { private $id; private $object_type; //0 for Article, 1 for Shop, 2 for Category private $object_id; ... private $nr_alltime_visits; } </code></pre> <p>Then I have tried with the following query in the article repository:</p> <pre><code> $query = $this->createQueryBuilder('a', 's', 'vc') ->select('a, s') ->leftJoin('a.shop', 's') ->leftJoin('ProjMyBundle:VisitCounter', 'vc', 'WITH', 'vc.object_type = 0 AND cs.object_id= a.id') ->where('a.is_published = 1 AND s.is_published = 1) ->orderBy('vc.nr_alltime_visits', 'DESC') ->groupBy('a.shop'); </code></pre> <p>But for I didn't get the articles expected, if I remove the group by I get the right articles but more than 1 per Shop.</p> <p>Any Ideas?</p> <p>Before this problem I had a field in Shop and Article(private $nr_visits), and then I had something like this(could help someone in the future):</p> <pre><code> $query = $this->createQueryBuilder('a', 's') ->select('a, s') ->where('a.is_published = 1 AND s.is_published = 1') ->leftJoin('a.shop', 's') ->groupBy('a.shop') ->orderBy('nr_visits', 'DESC'); $query->andWhere('a.nr_visits = (SELECT MAX(a2.nr_visits) as nr_visits2 FROM ProjMyBundle:Article AS a2 WHERE a2.is_published = 1 AND a2.shop=a.shop GROUP BY a2.shop)'); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload