CODEWITHSUNDEEP
phpsqllaravel
luisfer
luisfer

Reputation: 2130

Laravel: When doing an LEFT JOIN how to make alias of fields so I can access fields with the same name

I have a "master" table, lets say is something like:

id       autoincrement
name     varchar(10)
category integer

Then I have a "detail" table, lets say:

id        autoincrement
master_id integer
qty       integer

I want to make a LEFT JOIN like:

SELECT * FROM master
LEFT JOIN detail ON master.id = detail.master_id
WHERE category=1

This query produces exactly what I want in SQL: A table with all the master records even if they don't have detail, in that case will get me the detail columns with NULL which is correct.

Now in Laravel, I want to do something like:

$records = Master::where('category', '=', 1)
             ->leftJoin('detail', 'master.id', '=', 'detail.master_id')

If the detail table is empty, it returns id as NULL

I think this is because it returns id twice, first the master.id, and then the second id which comes from the detail table, is, of course, NULL

So how can I alias the first (or second id) in order to access it?

Upvotes: 0

Views: 1414

Answers (1)

nakov
nakov

Reputation: 14288

You can get all the columns from the master table, and select and alias each of the details table columns that you need separately in a select. You are right that it overrides them because of the same names. So try this:

$records = Master::where('category', '=', 1)
             ->select('master.*', 'detail.id as detail_id')
             ->leftJoin('detail', 'master.id', '=', 'detail.master_id')

Keep in mind that this will just show the detail table ID in the results along with all the master table columns, you should add the other columns from the detail table as a select parameters.

Upvotes: 1

Related Questions