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.
后端开发经常会需要用到条件查询，where() 可以很好的完成基本的属性筛选，不过如果我们需要获取的数据是附带有关系表的（一对多，多对一等），并且希望能够根据关系表中的数据限制查询结果，我们就需要做一些进一步的工作。
Dev Env
Framewrok: Php Laravel 5.8
Refer Solutions on Laravel Doc
Credit: https://laravel.com/docs/5.8/eloquent-relationships
- Load a relationship, but also specify additional query conditions for the eager loading query.
$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
- 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.
- 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);
- 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.
Refer Link
- Laravel Eloquent Documentation
- Laravel where on relationship object
- Laravel: Limit Nested Relationships
- Laravel ORM 三表关联查询（with）
This blog is under a CC BY-NC-SA 3.0 Unported License
本文使用 CC BY-NC-SA 3.0 中国 协议许可
署名-非商业性使用-相同方式共享（BY-NC-SA）：使用者可以对本创作进行共享、演绎，但使用时须进行署名（同时标明是否（对原始作品）作了修改），且不得运用于商业目的，采用本创作的内容必须同样采用本协议进行授权。详情请参考协议细则。
本文链接：https://dataleoz.com/laravel-eloquent-limit-results-based-on-relationship/