Problem with DB query self referencing

Hi all,

I cannot remember how to self reference a table with a DB query(and not an eloquent model). The goal is that the user who owns the ads from the AD_USER table sees all his ads in a table while also outputting in the same table the manager name and surname who takes care of his account. But because this will be an ADMIN view, the admin must be able to see all the ads and the manager's details per ad in a single table. I am nearly there, It is just this self reference manager_id which I cannot get to through a DB query

My problem is the self referencing of the manager_id which loops back to the same USERS table. This way: AD_USER table > reference to > USERS table >reference to> USERS table(the user who owns the ad) > the user table references to> MANAGER_ID (which is turn should loop back to the user table to get his name and surname.

I have these tables:

AD_USER
ad_id
user_id
budget

ADS
-id
-name
-price

USERS
-id
-name
-surname
-manager_id
-sales

This is a short version of my total query

DB::table('ad_user')
->leftJoin('ads', 'ads.id', '=', 'users.id')
->leftJoin('users', 'users.id', '=', 'users.manager_id')//HERE
->where('ad_user.user_id', Auth::id())
->select('ads.*', 'users.*', 'manager_name', 'manager_surname') //ideally
->get()

I am looking for an output with: -All the ads data -All the users data -The name and surname of the manager(from the users table self reference)

I hope I explained it well :-)

Thanks!

lbecket

3 years ago

Best Answer

Level 39

I'm struggling to understand the relationships that you're describing, but it sounds like your need could be accommodated by joining the users table a second time and aliasing it... I'm just not following what that join logic is supposed to be. Regardless, the shape of it might be something like this:

DB::table('ad_user')
->leftJoin('ads', 'ads.id', '=', 'users.id')
->leftJoin('users', 'users.id', '=', 'users.manager_id')
->leftJoin('users as managers', 'ad_user.id', '=', 'managers.id')
->where('ad_user.user_id', Auth::id())
->select('ads.*', 'users.*', 'managers.manager_name', 'managers.manager_surname')
->get()

1 like

LaraBABA

3 years ago

Level 10

@lbecket You got it, thank you so much! I did not think about that!!

Please or to participate in this conversation.