Laravel: How to limit results based on relationship (有条件的关联查询)

During the backend dev, sometimes we need to limit our query based on some situations, normally where() will work. But if the results are with some relationship tables and we want to limit these results based on some attribute in relationship, we need to do some further work.

## Dev Env

Framewrok: Php Laravel 5.8

## Refer Solutions on Laravel Doc

$users = App\User::with(['posts' => function ($query) {
$query->where('title', 'like', '%first%'); }])->get();  In this example, Eloquent will only eager load posts where the post’s title column contains the word first. Cons: we still get the results with no ‘posts’ result 1. Querying Relationship Existence When accessing the records for a model, you may wish to limit your results based on the existence of a relationship. For example, imagine you want to retrieve all blog posts that have at least one comment. To do so, you may pass the name of the relationship to the has and orHas methods: // Retrieve all posts that have three or more comments...$posts = App\Post::has('comments', '>=', 3)->get();


Cons: If the we want to limit it in a belongsTo relationship, it seems can’t work well.

## My Solution

Solutions above can solve most of relationship limitation problems. But I still want to share two personal methods that can solve limitation with a belongs to relationship.
Assume a relationship like below:
A recurring schedule belongs to users, and this shcedule has many single date events(one event is belongs to one schedule)

• Schedule(id, user_id, …)
• Events(id, schedule_id, …)

Now, if we want to query all events belons to current user, we have two methods.

1. Eloquent ORM

Result will show normal relationship JSON format

// Get current user
$user = auth('api')->user(); // Get schedules belongs to current user$schedule_ids = Schedule::where('user_id', $user->id)->get()->pluck('id'); // events' schedule_ids are in above schedule_ids arrays$events = ScheduleEvent::with(['schedule', 'schedule.child:id,name'])->whereIn('schedule_id', $schedule_ids);  1. DB Functions Result will put all attributes together in one Object $events = DB::table('schedule_events')->leftJoin('schedules', 'schedules.id', 'schedule_events.schedule_id')->leftJoin('children', 'schedules.child_id', 'children.id')->where('schedules.user_id', \$user->id)->orderBy('date')->orderBy('pick_time');


As you can see, for belongsTo relationship result limitation, not a normal single function can done in Laravel, but we can still solve it someway, either DB or Eloquent.

This blog is under a CC BY-NC-SA 3.0 Unported License