Doctrine 2 Self Join Query

Question

I'm still pretty new to Doctrine and I'm trying to retrieve a suggest list of users to follow.

So basically, given an user A, I need to select all users that are followed by the users that user A follow, excluding users that user A already follow.

How could I do that with Doctrine query builder ?

class User
{

...

/**
 * @ORM\ManyToMany(targetEntity="User", inversedBy="followees")
 * @ORM\JoinTable(name="user_followers",
 *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id", onDelete="CASCADE")},
 *      inverseJoinColumns={@ORM\JoinColumn(name="follower_id", referencedColumnName="id", onDelete="CASCADE")}
 *      )
 */
private $followers;

/**
 * @ORM\ManyToMany(targetEntity="User", mappedBy="followers")
 */
private $followees;
}

EDIT: According to slaur4 solution, I tried this

    $qb = $this->createQueryBuilder('u');

    $qb->select('suggestions')
    ->join('u.followees', 'followees')
    ->join('followees.followers', 'suggestions')
    ->where('u.id = :id')
    ->andWhere($qb->expr()->notIn('suggestions.id', 'u.followees'))
    ->setParameter('id', $user->getId());

But it gives me the following exception:

QueryException: [Syntax Error] line 0, col 171: Error: Expected Literal, got 'u'

It's just an User entity as well. I mean there's no UserFollowers entity, just a descriptive table for the many-to-many relationship between User entities — Remi M, Jan 20 '15 at 15:16

slaur4 · Accepted Answer · 2015-01-22T10:40:11.040

1

It's a self-referencing query. I would try this :

QueryBuilder (User Symfony2 repository)

<?php

//Subquery to exclude user A followees from suggestions
$notsQb = $this->createQueryBuilder('user')
    ->select('followees_excluded.id')
    ->join('user.followees', 'followees_excluded')
    ->where('user.id = :id');

$qb = $this->createQueryBuilder('suggestions');
$qb->join('suggestions.followers', 'suggestions_followers')
    ->join('suggestions_followers.followers', 'users')
    ->where('users.id = :id')
    ->andWhere('suggestions.id != :id') //Exclude user A from suggestions
    ->andWhere($qb->expr()->notIn('suggestions.id', $notsQb->getDql()))
    ->setParameter('id', $userA->getId());
$query = $qb->getQuery();
$users = $query->getResult(); // array of User

edited Jan 22 '15 at 10:40

answered Jan 20 '15 at 16:29

slaur4

494
3
11

I have edited my answer because `notIn('suggestions.id', 'u.followees')` will not work as expected – slaur4 Jan 21 '15 at 10:37
Gave me this `QueryException: [Semantical Error] line 0, col 253 near 'followees WHERE': Error: 'followees' is already defined.` So I changed the name in the notsQb and now have this `QueryException: [Semantical Error] line 0, col -1 near 'SELECT suggestions': Error: Cannot select entity through identification variables without choosing at least one root entity alias.` – Remi M Jan 21 '15 at 10:56
Your last edit is free of exceptions! :) You might find it funny (or not!), the only person this complex query gives me back is myself, the user A! – Remi M Jan 21 '15 at 11:38
I have one follower who has no follower and I follow two. The two I follow have more than 200 followers. These are the people I would like to be suggested about. Shouldn't we start by joining the followees instead of the followers? – Remi M Jan 21 '15 at 13:33
It gives me an empty result. Can you explain more how you see it? I get the joins but not so much the `->where('users.id = :id')` line. As I understand it, it asks for current user ID to match with IDs of followees of followees. – Remi M Jan 21 '15 at 15:35
`->where('users.id = :id')` limits results to a specific user. Else you will get followees of followees of all users. I have modified the query. – slaur4 Jan 22 '15 at 10:40
Had to wait today to try but yep that's working! Thanks a lot for your help slaur! :) – Remi M Jan 26 '15 at 10:01
1

Well, sorry for the multiples edits, I should have tried it first before posting an answer. I'm glad it finally worked – slaur4 Jan 26 '15 at 10:04

Doctrine 2 Self Join Query

1 Answers1

Linked