Laravel and Eloquent: Specifying columns in when r

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?

Answer 1

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

Answer 2

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.