Fetch multiple tables through hasManyThrough Laravel Eloquent

Question

I'm kind of new to databases and I made this small database, but I have problems fetching data from it. Im trying to get all the racers from the logged in user, and it works properly, but if I enter $pigeons = $user->racer I only get back the racer table. I would like to know the attributes of the racers from the pigeons table aswell. I've made it work with query builder left joining the tables but I'm not sure why I set up this relationship if I can't use Laravel inner method.

In the User model I have these relationships:

public function pigeons(){
        return $this->hasMany('App\Pigeon');
    }

    public function racers(){
        return $this->hasManyThrough('App\Racer', 'App\Pigeon');
    }

This is the Pigeon model:

public function user(){
        return $this->belongsTo('App\User');
    }

    public function racer(){
        return $this->hasMany('App\Racer');
    }
}

And this is the Event model:

public function race(){
        return $this->hasOne('App\Race');
    }

    public function racers(){
        return $this->hasMany('App\Racer');
    }

And this is what my EventsController looks like with the working alternative method and the commented not working.

public function upcoming(){
        $id = auth()->user()->id;
        $user = User::find($id);
        $pigeons = DB::table('racers')->leftJoin('pigeons', 'racers.pigeon_id', '=', 'pigeons.id')->where('pigeons.user_id', '=', $id)->get();

        //$pigeons = $user->racers;

        return view('events.upcoming')->with('pigeons', $pigeons);
    }

This is what I get with $user->racers or $user->racers()->get():

[{"id":1,"pigeon_id":14,"user_id":4,"event_id":1,"position":0,"created_at":null,"updated_at":null},{"id":2,"pigeon_id":15,"user_id":4,"event_id":1,"position":0,"created_at":null,"updated_at":null},{"id":3,"pigeon_id":16,"user_id":4,"event_id":1,"position":0,"created_at":null,"updated_at":null}]

And this is what I want to get, its not correct either since I should get id:1 but I want to pass to view these additional datas aswell like gender, color, ability (but they are in pigeons table not in racers).

[{"id":14,"pigeon_id":14,"user_id":4,"event_id":1,"position":0,"created_at":"2018-09-27 10:01:04","updated_at":"2018-09-27 10:01:04","gender":"hen","color":"blue","ability":38},{"id":15,"pigeon_id":15,"user_id":4,"event_id":1,"position":0,"created_at":"2018-09-27 10:01:04","updated_at":"2018-09-27 10:01:04","gender":"hen","color":"blue","ability":48},{"id":16,"pigeon_id":16,"user_id":4,"event_id":1,"position":0,"created_at":"2018-09-27 10:01:04","updated_at":"2018-09-27 10:01:04","gender":"cock","color":"blue","ability":11}]

Try adding the results of your current code and an example of the result you want to get — Mike Miller
This is fairly hard to answer because you mention you want racers from the logged in user but the racers table has a user_id in it already. Why are you trying to left join pigeons and constrain on the user_id that way? What's the problem with using Racer::where('user_id', $userId); for example? Then you have results for what you want to get but then say it's not what you want because id of 1. But those are the id's of the pigeon, not the user. — user1669496
Take a look at BelongsToMany relationships: laravel.com/docs/eloquent-relationships#many-to-many — Jonas Staudenmeir
I've figured out the problem with ids, they were the same column name after the join and the second one overwrote the first one. One problem solved but I'm still stuck with eloquel fetching data. The problem is that I can get the racers data, even with the correct user, but I don't know how I can get additional data after I fetched that table, like how can I get the pigeons info from their pigeon_id. — shawnest

Ojo Tokunbo Ojo Tokunbo · Accepted Answer · 2018-09-27T12:51:36

To get the pigeons, what you would have to do is $pigeons = $user->racers()->get();. You can see an example of this in Laravel's official documentation https://laravel.com/docs/5.5/eloquent-relationships#introduction.

Fetch multiple tables through hasManyThrough Laravel Eloquent

1 Answers