Laravel eloquent with() nested relationship fields in parent unnested

Question

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?

score 1 · Accepted Answer · answered Feb 15 '22 at 09:22

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.

Dennis Maina · Answer 2 · 2022-02-10T18:06:43.323

0

To answer the question "How can i get specifics columns from a query that uses with()?"

Try writing with() with a callback function like this:

$rows = ProjectLogin::with( 'User:PRIVATE,naam', function($query){
    $query->select('column_name');
})
->with( 'Project:PRIVATE,naam', function($query){
    $query->select('column_name');
})
->with( 'Queue:PRIVATE,queue_naam', function($query){
    $query->select('column_name');
})
-> with( 'AgentStatus:PRIVATE,status', function($query){
    $query->select('column_name');
})

Notice how i separated the with() into different lines, this is because when you pass a callback function to the function it can only take one parameter.

On the issue of the array format, I'm not sure this will work, but try putting them into a collection and flatten that collection to give you the desired state.

$flattened = $collection->flaten()

The variable $collection holding your eloquent collection results.

edited Feb 10 '22 at 18:06

answered Feb 10 '22 at 17:48

Dennis Maina

202
1
8

1

Instead of multiple `->with()` clauses, use `->with()` with an array: `->with(['User:PRIVATE,naam' => function ($query){ ... }), 'Project:PRIVATE,naam' => function ($query) { ... }, ...])`. That was the main mistake in the query. This _works_, but is horrendous to look at (in my experience anyway) – Tim Lewis Feb 10 '22 at 17:51
this is not the solution. you cant use User:PRIVATE,naam in a with if you use a callback, it errors. you also need to mention the foreign key in the select. so my code looks like this, but it return the same back as how it had it previous without the callback. the relations are in seprate arrays in the array: `$rows = ProjectLogin::` `with(['User' => function($query)` `{` `$query->select('PRIVATE','naam AS user_name');` `}])` `->limit(10)->get();` – fidderr Feb 11 '22 at 09:19
thats partly my fault, i updated my main question to my specific need: **How can i get specifics columns from a query that uses with() in the main array instead of nested arrays?** – fidderr Feb 11 '22 at 09:19

Laravel eloquent with() nested relationship fields in parent unnested

2 Answers2