Wes Murray Wes Murray - 2 months ago 6
MySQL Question

Laravel: query is only fetching one related id

Im trying to get all id's that are related to a specific list but my query is only returning the first related id in the table and not the others.

Tables

List | id | person_id | name | description
| 1 | 10 | Test List | null

List_Ref | id | list_id | data_id
| 1 | 1 | 100
| 2 | 1 | 101


Query

$lists = DB::table('List')
->leftJoin('List_Ref', 'List_Ref.list.id', '=', 'List.id')
->select('List.id', 'List.name', 'List_Ref.data_id')
->where('person_id', Auth::user()->person_id)
->groupBy('List.id')
->orderBy('List.id')
->get();


Result (Laravel Die and Dump)

#items: array:1 [
0 => {
"id" : 1
"name" : "Test List"
"data_id" " 100
}
]


I would like to produce a result like the following

#items: array:1 [
0 => {
"id" : 1
"name" : "Test List"
"data_id" => {
"id" : 100
"id" : 101
}
}
]

Answer

Don't use groupBy('List.id') It will group the data on the basis of id of List table & always return only single data.

Comments