Laravel and Eloquent: Specifying columns in when retrieving related items

Question

This is a followup post to: Laravel 4 and Eloquent: retrieving all records and all related records

The solution given works great:

$artists = Artist::with('instruments')->get();
return \View::make('artists')->withArtists($artists);

It also works with just:

$artists = Artist::get();

Now I'm trying to specify the exact columns to return for both tables. I've tried using select() in both the statement above and in my Class, like this:

ArtistController.php

$artists = Artist::select('firstname', 'lastname', 'instruments.name')->get();

or:

$artists = Artist::with(array('instruments' => function($query) {
    $query->select('name');
}))->get();

(as suggested here and while this doesn't throw an error, it also doesn't limit the columns to only those specified)

or in Artist.php:

return $this->belongsToMany('App\Models\Instrument')->select(['name']);

How would I go about getting just the firstname and lastname column from the artists table and the name column from instruments table?

Roughneck

Your problem is that you have to load the primary key and the foreign key. Otherwise the ORM can't load your relationships. – Lamellar

@JosephSilber Your comment helped me! So, live happy in the knowledge that you've helped at least one individual with their development! :] – Acaulescent

Answer 1

Not sure what I was thinking. I think working on this so long got me cross-eyed.

Anyhow, I looked into this a lot more and searched for answers and finally posted an issue on GitHub.

The bottom line is this is not possible as of Laravel v4.1.

https://github.com/laravel/laravel/issues/2679

This solved it:

Artists.php

public function instruments() {
    return $this->hasMany('App\Models\Instrument', 'id');
}

Note that I changed this to a hasMany from a belongsToMany which makes more sense to me as a musicians (or Artist) would have many Instruments they play and an Instrument could belong to many Artists (which I also alluded to in my previous questions referenced above). I also had to specify 'id' column in my model which tells the ORM that instrument.id matches artist_instrument.id. That part confuses me a bit because I thought the order for hasMany was foreign_key, primary_key, but maybe I'm thinking about it backwards. If someone can explain that a bit more I'd appreciate it.

Anyhow, the second part of the solution...

In ArtistsController.php, I did this:

$artists = Artist::with(array(
    'instruments' => function($q) {
        $q->select('instruments.id', 'name');
    })
)->get(array('id', 'firstname', 'lastname'));

That gives me exactly what I want which is a collection of Artists that contains only the firstname and lastname columns from the artists table and the name column for each of the instruments they play from the instruments.

Roughneck

"Limiting the columns on a belongsToMany relationship is not currently supported." – Originally

Answer 2

$artists = Artist::with(array('instruments' => function ($query) {
    $query->select('id', 'name');
}))->get('id', 'firstname', 'lastname');

Lamellar

This throws an error saying the id columns are ambiguous. If I do this: instruments.id and artists.id then it doesn't fail, but it doesn't limit the columns. In this case though, instruments.id is a foreign key of instrument_id in artist_instrument and artists.id is a foreign key of artist_id in artist_instrument. – Roughneck