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

Author Avatar
DataLeoZ 8月 15, 2019
  • 在其它设备中阅读本文章

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


  1. 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%');

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', '', 'schedule_events.schedule_id')->leftJoin('children', 'schedules.child_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.

  1. Laravel Eloquent Documentation
  2. Laravel where on relationship object
  3. Laravel: Limit Nested Relationships
  4. Laravel ORM 三表关联查询(with)

This blog is under a CC BY-NC-SA 3.0 Unported License
本文使用 CC BY-NC-SA 3.0 中国 协议许可