The difference between of has and with when the filter result is null in Laravel correlation model

Look at the code first:

$userCoupons = UserCoupons::with(['coupon' => function($query) use($groupId){
 return $query->select('id', 'group_id', 'cover', 'group_number', 'group_cover')->where([
   'group_id' => $groupId,
 ]);
}])
//  More queries omitted ...

The data structure is three tables: user coupon table (user_coupons), coupon table (coupons), merchant table (corps) and group coupon table (group_coupons) (the latter two items have been removed for convenience of viewing)

Here, I intended to use model association to find out all the data in the user coupon belonging to a given group gourpId (if it is empty, the data will not be returned).

But some results are not what I want:

array(20) {
 ["id"]=>
 int(6)
 ["user_id"]=>
 int(1)
 ["corp_id"]=>
 int(1)
 ["coupon_id"]=>
 int(4)
 ["obtain_time"]=>
 int(1539739569)
 ["receive_time"]=>
 int(1539739569)
 ["status"]=>
 int(1)
 ["expires_time"]=>
 int(1540603569)
 ["is_selling"]=>
 int(0)
 ["from_id"]=>
 int(0)
 ["sell_type"]=>
 int(0)
 ["sell_time"]=>
 int(0)
 ["sell_user_id"]=>
 int(0)
 ["is_compose"]=>
 int(0)
 ["group_cover"]=>
 string(0) ""
 ["is_delete"]=>
 int(0)
 ["score"]=>
 int(100)
 ["created_at"]=>
 NULL
 ["updated_at"]=>
 NULL
 ["coupon"]=>
 NULL //  Attention returned coupons Null data 
}

Some coupon records have records, while others are empty. Think about it, too. with is just a so-called preload implemented with in () of sql. In any case, the data of main user_coupons will be listed.

It will have two sql queries, one for master data and two for association. Here, the second sql is as follows:

select `id`, `group_id`, `cover`, `group_number`, `group_cover` from `youquan_coupons` where `youquan_coupons`.`id` in (1, 2, 3, 4, 5, 7, 8, 9, 10, 11, 13, 14) and (`group_id` = 1) and `youquan_coupons`.`deleted_at` is null

If Article 2 is blank, the associated field of the master record is NULL.

Later, we saw the has () method of the Laravel associated model. has () is based on the existing associated query. Next, we use whereHas () (1 function, but more advanced and convenient to write conditions)

Here, our idea is to judge whether there is coupon data in the first query logic, so we can filter empty records.

The code after adding whereHas () is as follows

$userCoupons = UserCoupons::whereHas('coupon', function($query) use($groupId){
  return $query->select('id', 'group_id', 'cover', 'group_number', 'group_cover')->where([
   'group_id' => $groupId,
  ]);
 })->with(['coupon' => function($query) use($groupId){
  return $query->select('id', 'group_id', 'cover', 'group_number', 'group_cover');
 }])-> // ...

Look at the final SQL:

select * from `youquan_user_coupons` where exists (select `id`, `group_id`, `cover`, `group_number`, `group_cover` from `youquan_coupons` where `youquan_user_coupons`.`coupon_id` = `youquan_coupons`.`id` and (`group_ids` = 1) and `youquan_coupons`.`deleted_at` is null) and (`status` = 1 and `user_id` = 1)

Here, exists () is actually used to filter the existing records. Then go down the with () query in step 1, because it has been filtered once at this time, with can remove the condition.

Obviously, it is important to distinguish between these two, especially in the list, it is not necessary to filter empty data specially, and it is easy to do paging.

Summarize