3

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?

Community
  • 1
  • 1
tptcat
  • 3,894
  • 2
  • 32
  • 51
  • 1
    Your problem is that you have to load the primary key and the foreign key. Otherwise the ORM can't load your relationships. – Joseph Silber Feb 12 '14 at 19:26
  • @JosephSilber Your comment helped me! So, live happy in the knowledge that you've helped at least one individual with their development! :] – Azirius Apr 14 '14 at 16:12

2 Answers2

3

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.

tptcat
  • 3,894
  • 2
  • 32
  • 51
2
$artists = Artist::with(array('instruments' => function ($query) {
    $query->select('id', 'name');
}))->get('id', 'firstname', 'lastname');
Joseph Silber
  • 214,931
  • 59
  • 362
  • 292
  • 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`. – tptcat Feb 12 '14 at 19:39