The difference between has and with in Laravel association model (detailed introduction)

The content of this article is about the difference between has and with in Laravel association model (detailed introduction). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

First look at the code:

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

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

Here I originally intended to use model association to find out all the data belonging to the given group gourpId in the user coupons (if it is empty, the data will be not 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  // 注意返回了coupons为空的数据
  }

Some coupons in the records have records, and some are empty. Think about it, with is just the so-called preloading implemented using sql's in(). No matter what the main user_coupons data will be listed.

It will have two sql queries, the first one checks the main data, the second one checks the association. The second sql here 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 the second one is empty, the main record The associated field is NULL.

Later I saw the has() method of Laravel's associated model. has() is based on the existing associated query. Below we use whereHas() (the same effect, just more advanced, convenient for writing conditions)

Our idea here is to put the judgment of whether there is coupon data in the first query logic, so that we can filter out 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 actually uses exists() to filter existing records. Then go to the next step of the with() query. Because everything has been filtered at this time, with can remove the condition.

Obviously it is important to distinguish between the two functions, especially in lists. There is no need to specifically filter out empty data, and it is easy to do paging.

The above is the detailed content of The difference between has and with in Laravel association model (detailed introduction). For more information, please follow other related articles on the PHP Chinese website!