symfony (doctrine): DQL query, how to exclude results?

Question

I have very basic knowledge of SQL and don't how to modify my query so it returns the expected result.

My entities Activity has a field members (a manyToMany relation). An activity can be done by several members. I want to select all the activities a specific member does NOT take part into. I know the member's id (my variable $selfId).

Here's what I'm doing in ActivityRepository:

public function getCollectiveActivities($selfId)
{
    $qb = $this->createQueryBuilder('a');
    $qb->join('a.members', 'm')
       ->where('m.id != :selfId')
       ->setParameter('selfId', $selfId);

    return $qb
        ->getQuery()
        ->getResult();
}

My problem: when an activity "owns" two or more members, if one of them has $selfId as id, this activity ends up in my query results. But since one of the members of this activity has the id $selfId, it should not be returned. What am I doing wrong?

EDIT

I think I need to be more restrictive, I just don't know how. Let's say I have two activities:

activity1 which is owned by member1 and member2
activity2 which is owned by member3 and member4

$selfId = 3 (this means I don't want to fetch activities owned by member3)

From what I understand, the join clause might return lines like these:

activity1 | memberId: 1 (different from selfId = 3 so activity1 will be fetched)
activity1 | memberId: 2 (different from selfId = 3 so activity1 will be fetched)
activity2 | memberId: 3 (= selfId so activity2 shouldn't be fetched)
activity2 | memberId: 4 (different from selfId = 3 so activity2 WILL be fetched. PROBLEM???)

EDIT 2

Others already faced the same problem and found this solution and this one, but they seem a bit hacky. Any clue on how to improve them would be welcome.

score 2 · Answer 1 · answered Apr 15 '16 at 03:28

You have to specifically select the results you want to be hydrated. The problem you're seeing is that you're just selecting activity.

Then when you call $activity->getMembers() members are lazy loaded, and this doesn't take into account your query.

You can avoid this like so:

public function getCollectiveActivities($selfId)
{
    $qb = $this->createQueryBuilder('a');
    $qb->addSelect('m');
    $qb->join('a.members', 'm')
       ->where('m.id != :selfId')
       ->setParameter('selfId', $selfId);

    return $qb
        ->getQuery()
        ->getResult();
}

This means your fetched activity will already have its members hydrated and restricted by your query condition.

Thanks for your answer, but adding `$qb->addSelect('m');` doesn't change anything to my query result. I still get the unwanted activity. — Roubi, Apr 15 '16 at 08:48

score 0 · Answer 2 · edited Feb 21 '17 at 18:09

0

public function getCollectiveActivities($selfId)
{

    return $this->createQueryBuilder('a')
        ->join('a.members', 'm')
        ->where($qb->expr()->neq('c.selfId', $selfId))     
        ->getQuery()
        ->getResult();

}

edited Feb 21 '17 at 18:09

Didier Ghys

30,396
9
75
81

answered Apr 15 '16 at 09:43

abhinand

554
5
10

'neq' is 'not equal to' , i hope which may help to complete your query. All the best. – abhinand Apr 15 '16 at 09:44
Thanks for your answer. You meant **->where($qb->expr()->neq('m.id', $selfId))** Anyway, I think using expr()->neq() is strictly equivalent to what I'm doing, it didn't change anything to my result. – Roubi Apr 15 '16 at 11:24

score 0 · Answer 3 · edited May 23 '17 at 11:53

I ended up adapting a solution posted by @NtskX.

public function getCollectiveActivities($selfId)
{
    $qbMyCollectives = $this->createQueryBuilder('ca');
    $qbMyCollectives
        ->select('ca.id')
        ->leftJoin('ca.members', 'm')
        ->where('m.id = :selfId')
        ->setParameter('selfId', $selfId);

    $qb = $this->createQueryBuilder('a');
    $qb
        ->where($qb->expr()->notIn('a.id', $qbMyCollectives->getDQL()))
        ->setParameter('selfId', $selfId); // I had to declare the parameter one more time

    return $qb
        ->getQuery()
        ->getResult();
}

I really thought someone would show up with a better way to do the join, so any other answer is much welcome.

symfony (doctrine): DQL query, how to exclude results?

EDIT

EDIT 2

3 Answers3