Doctrine select all users that manage other users via column

Question

Maybe I am missing something completely, but I cannot get it working. I only want to select User objects that are linked to User objects.

User:

class User implements AdvancedUserInterface, \Serializable
{
/**
 * @ORM\Id
 * @ORM\Column(type="integer")
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;


// other fields ...



/**
 * @ORM\ManyToOne(targetEntity="AppBundle\Entity\User")
 */
private $firstManager;

/**
 * @ORM\ManyToOne(targetEntity="AppBundle\Entity\User")
 */
private $secondManager;

}

I want to select those who are firstManager or secondManager for a User. Sounds simple eh?

I thought, this would do:

public function findAllManagers()
{
    $qb = $this->createQueryBuilder('user')
        ->join('user.firstManager', 'first_manager')
        ->join('user.secondManager', 'second_manager')
        ->orWhere('first_manager = user')
        ->orWhere('second_manager = user');
    $qb = $qb->getQuery();

    return $qb->getResult();
}

But only got one result, not all three I needed. I think this is valid SQL?

score 1 · Accepted Answer · answered Jun 08 '17 at 08:28

For what i see your sql query should look like:

SELECT user.* FROM user_table user
INNER JOIN user_table first_manager ON first_manager.id = user.first_manager_id
INNER JOIN user_table second_manager ON second_manager.id = user.second_manager_id
WHERE first_manager.id = user.id
OR second_manager.id = user.id

The result of this would be users who are their own first or second manager.

I think what you are looking for is this:

public function findAllManagers()
{
    $qb = $this->createQueryBuilder('m')
        ->leftJoin('u1', 'AppBundle\Entity\User', 'WITH', m = u1.firstManager)
        ->leftJoin('u2', 'AppBundle\Entity\User', 'WITH', m = u2.secondManager)
        ->where('u1.firstManager IS NOT NULL')
        ->orWhere('u2.secondManager IS NOT NULL')
        ->getQuery()
    ;

    return $qb->getResult();
}

The equivalent SQL query should be:

SELECT m.* FROM user_table m
LEFT JOIN user_table u1 ON u1.first_manager_id = m.id
LEFT JOIN user_table u2 ON u2.second_manager_id = m.id
WHERE u1.first_manager_id IS NOT NULL
OR u2.second_manager_id IS NOT NULL

Exactly! Didn't know the `WITH` command in a leftJoin statement. I think I add `DISTINCT` so I do not get duplicated results. — DelphiLynx, Jun 08 '17 at 09:19
The entityManager will handle duplication. The WITH keyword allows you to hard code the ON clause of the final SQL query. It is really useful on reverse-side of unidirectional mapping — OlivierC, Jun 08 '17 at 09:24

score 0 · Answer 2 · answered Jun 07 '17 at 15:27

0

->join() results in an INNER JOIN. As you can read here, this will only give results, that are "in the center". If you have multiple JOINs, this will only give results of all those joined tables. In your case, this means users that are firstManager and secondManager.

If you change to ->leftJoin(), this will give you all users (with additional info), so it is a good place to start. Then you can filter those out that are neither firstManager or secondManager.

Something like this should work (untested)

$result = $this->createQueryBuilder('user')
   ->leftJoin('user.firstManager', 'fm')
   ->leftJoin('user.secondManager', 'sm')
   ->where('fm.id IS NOT NULL')
   ->orWhere('sm.id IS NOT NULL')
   ->getQuery()
   ->getResult()
;

answered Jun 07 '17 at 15:27

kero

10,647
5
41
51

With the 'left join' clause it can either have 0, 1 or 2 managers. With the 'where' clause, when at least 1 manager exists, it must have an id (it is always the case). So, your basically selecting all the users here. – OlivierC Jun 08 '17 at 08:36
If you just left join, you will have `NULL` rows (except for user of course, but with no relation) - [like here](http://sqlfiddle.com/#!9/eda880/3). But if you add the `WHERE` clause, these will disappear ([code](https://pastebin.com/aQX6jW2t) - somehow sqlfiddle won't run it, but works). – kero Jun 08 '17 at 08:57
By the way, the resulting SQL of the posted DQL is exactly the same as yours – kero Jun 08 '17 at 09:00
You got the same SQL query than my second one ? It feels really weird... – OlivierC Jun 08 '17 at 09:07
Thanks kero, however what I want are those firstManagers and secondManagers objects. With your query I get those users _that have a manager_. But I want those managers only. I want all users that are manager, hence my functionname: `findAllManagers' – DelphiLynx Jun 08 '17 at 09:12
See the answer of OlivierC, you where going in the in te right direction, but the twist added by OlivierC did the job! – DelphiLynx Jun 08 '17 at 09:21

Doctrine select all users that manage other users via column

2 Answers2