如何使用Doctrine 2按特定的一个到多个字段的值排序

Question

I have an User, a Question and an Answer entity.

• Each User has n Answers.
• Each Answer has
  • an unique aid.
  • a question field holding the id of the Question answered.
  • the actual answer string given by the user, called answer.
• Each Question has a unique qid.

I want to select all Users ordered by their answer to a specific question. That's how far I got:

$qid = 123; // The id of the question to order the users by.
$orderByDir = 'ASC';

$qb->select('u')
   ->from('EventManager_Entity_User', 'u')
   ->leftJoin('u.answers', 'a', \Doctrine\ORM\Query\Expr\Join::WITH, 'a.question = ' . $qid)
   ->orderBy('a.answer', $orderByDir);

$users = $qb->getQuery()->execute();

But the result isn't ordered :/

Answer 1

I can't say for sure this is correct, but your query doesn't look quite right. Specifically the way you are joining Answers to Users. To me it looks like there is not foreign key relation between them in that join, even though there may be in your schema.

Try this, it may be incorrect for your schema, but you should be able to understand what is going on. Assuming the join is the issue, the ordering should work.

use Doctrine\Common\Collections\Criteria;

$qb
    ->select('u')
    ->from('EventManager_Entity_User', 'u')
    ->join('u.answers', 'a')
    ->join('a.question', 'q')

    ->where('q.id = :question_id')
    ->setParameter('question_id', $qid)

    ->orderBy('a.answer', Criteria::ASC)
;

I changed it to an inner join, without it some users not linked to answers not may be ordered like the rest.