duan205571
duan205571
2014-05-14 13:46
浏览 42
已采纳

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

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 :/

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dqwh0108
    dqwh0108 2014-05-14 14:04
    已采纳

    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.

    点赞 评论
  • dourou9477
    dourou9477 2014-05-14 14:07

    I would like to ask why don't you mention the relationships in your YAML or any other way you describe columns? Your tables, User-Answers got 1 to Many relationship and Question-Answers got 1 to Many relationship. If you mention the relationship using either YAML, annotation or etc, you don't need to mention the left join on what column. I think that cause problems when ordering. Because you are not specifically mentioning the relationships. If you have mentioned relationships correctly (try running php app/console doctrine:schema:valudate) you can reform your query to this,

    $qb->select('u')
       ->from('EventManager_Entity_User', 'u')
       ->leftJoin('u.answers','a')
       ->leftJoin('a.question','q')
       ->orderBy('a.answer', $orderByDir);
    

    Then to select the particular question, in your case $qid, you can do like this,

    $qb->select('u')
           ->from('EventManager_Entity_User', 'u')
           ->leftJoin('u.answers','a')
           ->leftJoin('a.question','q')
           ->where('q.id = :id')
           ->setParameter('id',$qid)
           ->orderBy('a.answer', $orderByDir);
    

    If you mention your relationships correctly and write your DQL like this, I am sure you will get the correct results. Hope this helps.

    Cheers!

    点赞 评论

相关推荐