i have 2 entity with one-to-many relation : Article
& ArticleCategory
class Article {
/**
* @var integer
*
* @ORM\Column(name="rate", type="integer",options={"default" : 0})
*/
private $rate = 0;
/**
* @var \ArticleCategory
*
* @ORM\ManyToOne(targetEntity="ArticleCategory",inversedBy="articles")
* @ORM\JoinColumn(name="article_category_id", referencedColumnName="id")
*/
private $category;
}
class ArticleCategory {
/**
*
* @var \Article
* @ORM\OneToMany(targetEntity="Article", mappedBy="category")
*/
protected $articles;
}
now i want to fetch categories which has much articles with top rates..
" i mean top N categories ordered by highest rated article in them (categories which has more articles with rate above the average rate)"
how can i do this?
finally i found this for my question, maybe it can be useful for others! :)
$query = $em->createQuery('SELECT c, avg(a.rate) x
FROM YoutabsGeneralModelBundle:ArticleCategory c
JOIN c.articles a
GROUP BY c.id
ORDER BY x DESC');
i add ORDER BY
because i wanted to set limit on this :
$query->setMaxResults($limit);