To get different values from mysql

Question

I have a problem with render different value from mysql. I just use laravel-7^ and I would like to render the column name and not the column id.

I have three tables: Day, agent, user.

In day table, there are this date:

ID	agent_id
1	1

In agent table, there are this date:

ID	user_id
1	5

In user table, there are this date:

ID	name
5	Marco

Now, I get the information because i use join method:

$days = DB::table('days')
       ->join('users', 'days.user_id', '=', 'users.id')
       ->join('users', 'days.agent_id', '=', 'users.id')
       ->select('days.id', 'users.name')
       ->get();

The first problem is that i can't use two times "users" table with join. The second problem is that i can't get the name of agent because days.agent_id = agents.id and agents.user_id = users.id.

Someone may help me? Thanks!

Answer 1

When building SQL queries, you need to provide unique aliases to tables (and columns), but your current query does not match the relationships suggested by your sample tables (days does not have user_id). I think it should be:

$days = DB::table('days')
       ->join('agents', 'days.agent_id', '=', 'agents.id')
       ->join('users', 'agents.user_id', '=', 'users.id')
       ->select('days.id', 'users.name')
       ->get();