Select only certain columns in related model when eager loading

Question

I have two related models:

class Attribute extends Eloquent
{
    public function categories()
    {
        return $this->hasMany('Category');
    }
}

class Category extends Eloquent
{
    public function attributes()
    {
        return $this->belongsTo('Attribute');
    }
}

I want to return all attributes and their categories as a JSON object, but I only want to select certain fields in both models (i.e. not return fields like 'created_at' in the JSON).

I've tried this:

$attributes = Attribute::select('id', 'name')
 ->with(['categories' => function ($query) {
   $query->select('categories.id', 'categories.name', 'categories.attribute_id');
 }]);

Response::JSON($attributes->get());

but despite the select query for the related model, unrequested fields are returned:

attributes: [{
 id: 3,
 name: "Organisation",
 categories: [{
  id: 3,
  name: "Asia HQ",
  attribute_id: 3,
  created_at: "2013-11-30 00:00:00",
  updated_at: "2013-11-30 00:00:00"
 }]
}]

How do I select only certain columns in related model when eager loading?

Similar thing was solved in http://stackoverflow.com/questions/14727038/laravel-eloquent-how-to-get-only-certain-columns-from-joined-tables — peter.babic, Jul 17 '14 at 11:04
Check what query was executed, because for `hasMany` it works as expected. — Jarek Tkaczyk, Jul 17 '14 at 11:11
@delmadord I understand I can set $hidden and $visible in the model. But I need to show different fields on different pages, so I'd like a dynamic solution. — mtmacdonald, Jul 17 '14 at 11:15
@MMacdonald You should check transformers - this is what you need. And again - the code you pasted is working as expected (L4.2), so either it's `belongsToMany` relation, which is bugged ineed, or you miss something. — Jarek Tkaczyk, Jul 17 '14 at 12:06

score 3 · Answer 1 · answered Mar 31 '17 at 22:47

Make use of select() laravel method:

$attributes = Attribute::select('id', 'name')->with(['categories' =>function($query){
        $query->select(['categories.id', 'categories.name','categories.attribute_id'])->get();
}]);

Response::JSON($attributes->get());

To make select method work in eager loading you need to include foreign_key in selected column list.

Reference : http://laravel-tricks.com/tricks/column-selection-in-eager-loading

score 2 · Answer 2 · answered Jul 17 '14 at 12:01

What about an eloquent method that will look like this?

public function dynamicAttributes($array_columns) {
  return $this->belongs_to('User')->select((array) $array_comuns);
}

Where $array_columns could be a string or array of strings representing a columns you want?

score 1 · Answer 3 · edited Mar 10 '20 at 11:02

If you want to always return those specific fields from the categories relation, defining the select in the relationship will work, but if you would like to do it on the fly for one specific query, ask Eloquent to only return those fields.

$attributes = Attribute::with(['categories' => function ($query) {
   $query->select('id', 'name', 'attribute_id');
 }])->get('id', 'name');

... Or you could use Fluent

DB::table('attributes')
            ->join('categories', 'attributes.id', '=', 'categories.attribute_id')
            ->select('attributes.id', 'attributes.name', 'categories.id', 'categories.name', 'categories.attribute_id')
            ->get();

score 0 · Answer 4 · answered Oct 21 '22 at 13:38

0

Try this:

$attributes = Attribute::select('id', 'name')
    ->with([
      'categories:categories.id,categories.name,categories.attribute_id',
    ]);

Response::JSON($attributes->get());

Please notice: no space after comma in argument list

answered Oct 21 '22 at 13:38

Savo

1
1

Select only certain columns in related model when eager loading

4 Answers4