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?

Answer 1

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.

Answer 2

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.