Doctrine2 :one-to-many relation;

Question

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?

you obviously need a rate field in your entity. You should begin by that, then use DQL or criterias to define what is a "top" rate. — mpm, May 05 '13 at 12:10
i have `rate` field in my `Article` Entity!see the question again! :) — parisssss, May 05 '13 at 12:15
Well if you write incomplete code dont expect people to guess for you , there is no annotations for the rate field , so it is not a doctrine orm field to me , as your question stands now. — mpm, May 05 '13 at 12:17
What does it mean "fetch categories which has much articles with top rates" ? You meant that you want get for example top N categories ordered by average of all ratings? Or top N categories ordered by highest rated article in them? Or all categories which have top-rated articles (eg. if top rate is 10, you'll get all categories which have an article rated with 10 rate)? Please, precise your question — Cyprian, May 05 '13 at 16:06
@Cyprian, thanks for your comment.i tried to clarify it again,by adding this to my question "i mean top N categories ordered by highest rated article in them (categories which has more articles with rate above the average rate )" — parisssss, May 06 '13 at 08:13

score 0 · Accepted Answer · answered May 08 '13 at 07:12

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

Doctrine2 :one-to-many relation;

1 Answers1