CODEWITHSUNDEEP
mysqlsqlsymfonydoctrine
a1337q
a1337q

Reputation: 780

Symfony2 & Doctrine: Order entites by foreign attribute

I would like to use a select to get an array of entities with Doctrines QueryBuilder. But I need an ORDER BY which uses a foreign attribute (attribute in a table related with a foreign key). What I would like to write intuitively is something like this:

$repo = $this->getDoctrine()->getRepository('MyBundle:relation_table');

$query = $repo->createQueryBuilder('r')
 ->orderBy('r.fevent.date', 'DESC')
 ->getQuery();

Which, not surprisingly, doesn't work. In SQL my SELECT looks like this:

SELECT r.* FROM relation_table AS r
INNER JOIN events AS e
    ON e.IDevent = r.Fevent
ORDER BY e.date

But I also need Doctrine to give me the entity-object back. I think of two possible solutions:

  1. Use the QueryBuilder to create an INNER JOIN, or
  2. Create a free SQL Statement, same as above, and tell Doctrine somehow to create an entity object with its results.

Any hints? Thanks.

Upvotes: 4

Views: 4230

Answers (2)

Elnur Abdurrakhimov
Elnur Abdurrakhimov

Reputation: 44831

You need to join the entity you want to order with:

$query = $repo->createQueryBuilder('r')
    ->join('r.fevent', 'f')
    ->orderBy('f.date', 'DESC')
    ->getQuery()
;

Upvotes: 2

Juan Sosa
Juan Sosa

Reputation: 5280

Joining both tables in your Doctrine Query (the first of your proposed solutions) is pretty straigtforward. Take a look here: http://symfony.com/doc/2.0/book/doctrine.html#joining-to-related-records

A similar question was answered here: Doctrine 2: How to search for an entity by its association's value?

Upvotes: 0

Related Questions