using pluck on nested results in Laravel-CodePudding

I have a query that returns the following results.

Illuminate\Database\Eloquent\Collection {#5145
  all: [
    App\Models\Result {#5207
        id: 198,
        result_id: 30,
        result_type: "App\Models\Touchpoint",
        audit_id: 1,
        weight: 7,
        pics: 0,
        recs: 0,
        rating: 4,
        comments: "none",
        complete: 1,
        created_at: "2022-06-03 03:42:24",updated_at: "2022-06-03 03:42:24",
        result: App\Models\Touchpoint {#5210
            id: 30,
            name: "Lineman",
            description: "The location food offer was available on Lineman",
            sort_order: 25,
            req_pics: 0,
            req_recs: 0,
            sector_id: 1,
            created_at: null,
            updated_at: "2022-04-02 14:02:34",
        },
    },
    App\Models\Result {#5119
        id: 199,
        result_id: 29,
        result_type: "App\Models\Touchpoint",
        audit_id: 1,
        weight: 7,
        pics: 0,
        recs: 0,
        rating: 4,
        comments: "none",
        complete: 1,
        created_at: "2022-06-03 03:43:38",
        updated_at: "2022-06-03 03:43:38",
        result: App\Models\Touchpoint {#5206
            id: 29,
            name: "Grab",
            description: "The location food offer was available on Grab",
            sort_order: 24,
            req_pics: 0,
            req_recs: 0,
            sector_id: 1,
            created_at: null,
            updated_at: "2022-04-02 14:02:26",
        },
    },
  ],
}

This is the query I'm using to get that collection and I want the result to just contain sort_order, name, description, rating and weight from these results and place them in an array. I'm assuming I need to use pluck to get the correct fields but when I try to pluck 'result.name', etc. I get told result doesn't exist.

$result = Result::query()->where('audit_id', 1)->where('result_type', 'App\Models\Touchpoint')->whereIn('result_id', $tps)->with('result')->get();

This needs to be in a query without manipulating the collection as I need to feed it into Maatwebsite\Excel\Concerns\WithMultipleSheets, which requires a query not the query results.

CodePudding user response：

You can use eager load constraints

$result = Result::query()
    ->where('audit_id', 1)
    ->where('result_type', 'App\Models\Touchpoint')
    ->whereIn('result_id', $tps)
    ->with('result:id,sort_order,name,description')
    ->select('id', 'result_id', 'rating', 'weight')
    ->get();

If you want to remove the nesting and flatten the result set you can map() over the collection

$result->map(function($item) {
    $item['sort_order'] = $item->result->sort_order;
    $item['name'] = $item->result->name;
    $item['description'] = $item->result->description;

    //remove the nested relation
    $item->unsetRelation('result');
})
->toArray();

/**
 * Will return something like
 [
    [
        'id' => 198,
        'result_id' => 30,
        'rating' => 4,
        'weight' => 7,
        'sort_order' => 25,
        'name' => 'Lineman',
        'description' => 'The location food offer was available on Lineman'
    ],
    [
        'id' => 199,
        'result_id' => 29,
        'rating' => 4,
        'weight' => 7,
        'sort_order' => 24,
        'name' => 'Grab',
        'description' => 'The location food offer was available on Grab'
    ]
]
*/

Laravel Docs - Eloquent Relationships - Constraining Eager Loads