Doctrine 2 - 如何将SQL ORDER连接表连接到另一个表

Question

I want to retrieve from database news and comments tables. This tables are related correctly. The problem is that I also want to ORDER comments table. Everything works include ordering(with ASC and DESC).

PHP CODE

$query = $doctrine->getManager()
->createQuery(
      'SELECT news FROM BlogAdminBundle:News news
       JOIN news.comments comments
       WHERE news.id = :id
       AND news.date_active < :date ORDER BY comments.dateAdd DESC'
)->setParameters(array('date' => new \DateTime(), 'id' => $news_id));

$fetched_news = $query->getSingleResult();
return array('fetched_news' => $fetched_news); 

TWIG CODE

{% for comment in fetched_news.comments %}
//displaying data
{% endfor %}

I know that I can retrieve separately news table and comments table, but idea to include this in one request is better idea.

Anyone want to help ?:)

Answer 1

This may work, assuming...

News entity looks something like this:

/**
 * News
 *
 * @ORM\Table(name="News")
 * @ORM\Entity()
 */
class News {
...
    /**
     * @ORM\OneToMany(targetEntity="comment", mappedBy="news", cascade={"persist", "remove"})
     * @ORM\OrderBy({"dateAdd" = "DESC"})
     */
    protected $comments;
...
}

Controller includes $news = $em->getRepository("YourBundle:News")->find($id);

And template includes (assumes column named item in News entity and column named comment in Comment entity):

{% for event in news %}
    {{ event.item }}
    {% for piece in event.comments %}
        {{ piece.comment }}
    {% endfor %}
{% endfor %}