laraveleloquentlaravel-query-buildereloquent-relationshiplaravel-relations

Laravel eloquent with() nested relationship fields in parent unnested


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?


Solution

  • 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.