the elequent query i have is:
$rows = ProjectLogin::with(
'User:PRIVATE,naam',
'Project:PRIVATE,naam',
'Queue:PRIVATE,queue_naam',
'AgentStatus:PRIVATE,status'
)
->limit(10)->get();
this will return this array if i do $rows->toArray():
array:10 [▼
0 => array:18 [▶]
1 => array:18 [▼
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => "PRIVATE"
"PRIVATE" => "PRIVATE"
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"user" => array:2 [▼
"PRIVATE" => PRIVATE
"naam" => "SOME NAME"
]
"project" => array:2 [▼
"PRIVATE" => PRIVATE
"naam" => "SOME PROJECT NAME"
]
"queue" => null
"agent_status" => array:2 [▼
"PRIVATE" => PRIVATE
"status" => "SOME STATUS"
]
]
2 => array:18 [▶]
3 => array:18 [▶]
4 => array:18 [▶]
5 => array:18 [▶]
6 => array:18 [▶]
7 => array:18 [▶]
8 => array:18 [▶]
9 => array:18 [▶]
]
the withs are in a array, but i dont want nested arrays, plus it contains the foreign keys which i dont need.
i want the array to look like for example:
array:10 [▼
0 => array:18 [▶]
1 => array:18 [▼
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => "PRIVATE"
"PRIVATE" => "PRIVATE"
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"PRIVATE" => PRIVATE
"user_naam" => 'SOME NAME'
"project_naam" => 'SOME NAME'
"agent_status_status" => 'SOME STATUS'
]
2 => array:18 [▶]
3 => array:18 [▶]
4 => array:18 [▶]
5 => array:18 [▶]
6 => array:18 [▶]
7 => array:18 [▶]
8 => array:18 [▶]
9 => array:18 [▶]
]
How can i get specifics columns from a query that uses with() in the main array instead of nested arrays?
i found 2 solutions to my issue.
the first solution is accepting relations are nested and making sure they return default fields with the "withDefault()" method. which would look like:
$rows = ProjectLogin::
with([
'User' => function($query) {
$query->select('PRIVATE','naam')
->withDefault(['PRIVATE' => '','naam' => '']);
},
'Project' => function($query) {
$query->select('PRIVATE','naam')
->withDefault(['PRIVATE' => '','naam' => '']);
},
'Queue' => function($query) {
$query->select('PRIVATE','queue_naam')
->withDefault(['PRIVATE' => '','queue_naam' => '']);
},
'AgentStatus' => function($query) {
$query->select('PRIVATE','status','status_colour')
->withDefault(['PRIVATE' => '','status' => '','status_colour' => '']);
},
]
)->get();
the other solution is if nested array are not a option is using "map()" which would look like:
$rows = ProjectLogin::with(
'User:PRIVATE,naam',
'Project:PRIVATE,naam',
'Queue:PRIVATE,queue_naam',
'AgentStatus:PRIVATE,status,status_colour'
)
->get()
->map(function($item) {
$item = $item->toArray();
return [
'PRIVATE' => $item['PRIVATE'],
'PRIVATE' => $item['PRIVATE'],
'PRIVATE' => $item['PRIVATE'],
'PRIVATE' => $item['PRIVATE'],
'user_name' => @$item['user']['naam'],
'project_name' => @$item['project']['naam'],
'queue_name' => @$item['queue']['queue_naam'],
'status' => @$item['agent_status']['status'],
'PRIVATE' => $item['PRIVATE'],
'status_colour' => @$item['agent_status']['status_colour'],
];
});
the $item = $item->toArray();
is important, otherwise it will create new queries that make n+1 obsolete.