Doctrine Nested Query to get Data from Table

Question

I had below use table User Table

| Id | Name | client_id |
|----|------|-----------|
| 1  | Emp1 | 256       |
| 2  | Emp2 | 256       |
| 3  | Emp3 | 257       |
| 4  | Emp4 | 258       |
| 5  | Emp5 | 258       |

and account_managers table

| Id | user_id | client_id | type |
|----|---------|-----------|------|
| 15 | 3       | 256       | A    |
| 16 | 4       | 257       | A    |
| 17 | 5       | 258       | A    |

Each User is account manager of an/some clients. I want to query the all upper level accounts manager of a single given user. for example in above relation and data.

Current User = 1

owner of 1 ------> is 3
owner of 3 ------> is 4
owner of 4 ------> is 5
owner of 5 ------> is 5

The last user Emp5 is itself account manager. this is where i have to stop looping over.

Below is how i find the account manager or given user, but after that I'm confused how to loop over again again to find their managers.

$queryBuilder = $this->createQueryBuilder('t');
$queryBuilder
    ->where('t.ownerTypeId = :ownerTypeId')
    ->andWhere('t.clientId IN(:clientIds)')
    ->setParameter('ownerTypeId', 'A')
    ->setParameter('clientIds', $clientIds)
;

SELECT * FROM account_team_owner a0_ 
WHERE a0_.owner_type_id = 'A' AND a0_.client_id IN ('256');

returns this record

| Id | user_id | client_id | type |
|----|---------|-----------|------|
| 15 | 3       | 256       | A    |

Now I want to find the account manager of user_id = 3, via same query. and so on. in one or two queries.

I don't completely follow your tables, but it sounds like you want a recursive hierarchical query. This problem has been [well covered before](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) here on Stack Overflow. — Tim Biegeleisen, Jul 06 '17 at 10:07
have a look at the different tree models there are, i think setting this up as a tree could make querying for this kind of data alot easier — Sam Janssens, Jul 06 '17 at 10:53
do you want last account manager or all of them ?Means you want only 5 or 1,3,4 and 5 in your example? — Bibhudatta Sahoo, Jul 06 '17 at 11:03

Doctrine Nested Query to get Data from Table

0 Answers0