Laravel Eloquent select only columns from relationship

Question

I have two models InvoiceTotal and MeterTariff with inside the InvoiceTotal model a one to many relationship

public function meter_tariff() {
    return $this->belongsTo(MeterTariff::class);
}

I'm querying all the unique meter_tariff rows based on invoice_id's using the with and distinct functions.

$validatedData = $request->validate([
    'invoice_ids' => ['required', 'array'],
    'invoice_ids*' => ['exists:invoices, id']
]);

$meter_tariffs = InvoiceTotal::with('meter_tariff')
    ->whereIn('invoice_id', $validatedData['invoice_ids'])
    ->distinct('meter_tariff_id')
    ->get();

This is working fine. The result is an array with unique rows based on the meter_tariff_id but I'm getting all the columns from InvoiceTotal and the MeterTariff relationship.

I want to limit the selected columns to id and name from the MeterTariff relationship.

I've tried the following code.

Select:

$meter_tariffs = InvoiceTotal::with('meter_tariff')
    ->whereIn('invoice_id', $validatedData['invoice_ids'])
    ->distinct('meter_tariff_id')
    ->select('meter_tariff:id,name')
    ->get();

Query inside the with:

$meter_tariffs = InvoiceTotal::with(['meter_tariff' => function($query) {
        $query->select('id', 'name');
    }])
    ->whereIn('invoice_id', $validatedData['invoice_ids'])
    ->distinct('meter_tariff_id')
    ->get();

Selection in the with:

$meter_tariffs = InvoiceTotal::with('meter_tariff:id, name')
    ->whereIn('invoice_id', $validatedData['invoice_ids'])
    ->distinct('meter_tariff_id')
    ->get();

Does this answer your question? [Get Specific Columns Using “With()” Function in Laravel Eloquent](https://stackoverflow.com/questions/19852927/get-specific-columns-using-with-function-in-laravel-eloquent) — miken32, Nov 12 '21 at 18:14
@miken32 I see some answers that I've tried. It returns specific columns of the relationship table but it also returns all the columns from the 'main' table and for this case I only need the columns from the relationship table and none of the main table — Thore, Nov 15 '21 at 07:58
@Thore , close to what you want, you may simply add `->map->meter_tariff` after ->get() — steven7mwesigwa, Nov 15 '21 at 08:11

score 0 · Answer 1 · answered Nov 12 '21 at 17:45

0

You can use pluck to get only the selected columns - id and name from the MeterTariff relationship.

$meter_tariffs = InvoiceTotal::with('meter_tariff')->whereIn('invoice_id', $validatedData['invoice_ids'])->distinct('meter_tariff_id')->get()->pluck('meter_tariff.id', 'meter_tariff.name')

answered Nov 12 '21 at 17:45

Nick

181
1
13

but `pluck` will return an array instead of collection. – Farhan Ibn Wahid Nov 13 '21 at 01:29
No. https://laravel.com/docs/8.x/collections#method-pluck – Nick Nov 14 '21 at 12:49
With pluck I'm getting an object instead of an array ```{"ALLE":1,"T03ILM":3,"T01DIR":4,"T17LVA":13,"T15L6P":15}``` Plus it seems limited to one/two values. If I add a third value it's ignoring the last one. – Thore Nov 15 '21 at 07:50

Laravel Eloquent select only columns from relationship

1 Answers1