Deeply nested relationships filtering

Question

Recently I faced problem accessing and filtering deeply nested relationship, so I decided to seek for a help.

So, I have this db structure: http://s21.postimg.org/motrjy3dj/Screenshot_from_2015_07_24_12_14_51.png

And I need to get all teams within a project, and then for each team I need to get assigned users (of that team).

So far so good, my problem starts when I try to get the offer for each user. User can have only one offer for the assigned team which brings me to a problem.

Here is my code:

$project = Project::with("variants")
                ->with(array(
                        "teams" => function($query) {

                            $query->with(array(

                                "users" => function($query) {

                                    $query->with("offers");
                                }
                            ));

                        }
                    ))
                ->find($projectID);

I have a hasManyThrough relationship "offers" in the "User" model which returns me all offers for user, but actually I just need (one) offer for related team_user table.

I tried filtering offers with scopes but it's a bad solution, because for each user I have additional query to db..

Is there some way to filter these offers dynamically?

Thanks!

Answer 1

I highly recommend using joins for this sort of complex query:

$projectOffers = Project
     ->join('team', 'team.project_id', '=', 'project.id')
     ->join('team_user', 'team_user.team_id', '=', 'team.id')
     ->join('user', 'user.id', '=', 'team_user.user_id')
     ->join('offer', 'offer.id', '=', 'team_user.offer_id')
     ->get([
         'project.id',
         'team.id AS team_id',
         'user.id AS user_id',
         'offer.id AS offer_id',
         // any other columns you want
     ]);