symfony2 multiple left join, inner join

Question

Using symfony2 but its more just mysql problem...

I think i have good table schema(just the important fields)(code is self explanatory):

users (id) statuses (id) likes (id,user_id,status_id) comments (id,user_id,status_id)

I am making big select.

Was trying this:

    $qb=$this->createQueryBuilder('s')
             ->addSelect('u')
             ->addSelect('u2')
             ->addSelect('u3')
             ->addSelect('l')
             ->addSelect('c')
             ->addSelect('s2')
             ->where('s.user = :user')
             ->setParameter('user', $user)
             ->innerJoin('s.user', 'u')
             ->leftJoin('s.likes', 'l')
             ->leftJoin('l.user', 'u2')
             ->leftJoin('s.comments', 'c')
             ->leftJoin('c.user', 'u3')
             ->leftJoin('c.status', 's2')
             ->orderBy('s.time', 'DESC')
             ->setMaxResults(15);

But the result is 15the same statuses... WRONG.

It worked well when i was selecting just statuses... likes to statuses.. and users(authors of likes and statuses)

Like that:

SELECT * FROM statuses s0_ 
INNER JOIN users t1_ ON s0_.user_id = t1_.id 
LEFT JOIN status_likes s2_ ON s0_.id = s2_.status_id
INNER JOIN users t3_ ON s2_.user_id = t3_.id  

WHERE s0_.user_id = 25 ORDER BY s0_.time DESC LIMIT 15

that works very nice but how can i implement the another comments and (users -> authors of comments) select THERE?...

Answer 1

You can't set max results on a joined query in the way you are attempting.

You need to use the paginator as described here: http://docs.doctrine-project.org/en/latest/tutorials/pagination.html

If it's not available in your version of Doctrine (you are pre 2.2) Then you can use the paginator here: https://github.com/beberlei/DoctrineExtensions

Another option is to not perform the join in the query - but simply allow the joined data to be fetched automatically. So iterate over users and traverse the object graph to get the infomation you require. This will obviously issue far more queries:

$users = $userRepository->findById($user);
$i = 0;
for ($users as $user){
    $likes = $user->getLikes(); 
    $comments = $user->getComments();
    $i++;
    if ($i == 15) { break; }
}

edit: changed the 'continue' to a 'break'; This is crappy code anyway - don't use it, I'm just illustrating an alternative.

Finally you could use a native query to perform this:

http://docs.doctrine-project.org/en/2.1/reference/native-sql.html

Edit: Similar question: Limiting a doctrine query with a fetch-joined collection?