How to filter the result set with empty result in Laravel association model

I would like to share with you how to filter the empty result sets in the Laravel association model. I believe most people don't know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

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,]) / / more queries are omitted.

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

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

But some of the 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 / / Note the data with empty coupons is returned}

Some coupon are recorded and some are empty in the record. Come to think of it, with is just a so-called preload implemented with sql's in (). The data for the master user_coupons will be listed no matter what.

It will have two sql queries, the first to check the master data and the second to check the association. Here the second sql is as follows:

Select `id`, `cover`, `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`.`cards _ at` is null

If the second entry is empty, the associated field of the master record is NULL.

Later, we saw the has () method of the model associated with Laravel. Has () is based on the existing association query, so let's use whereHas () (the same function, but more advanced, easy 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 with 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') }])-> /.

Take a look at the final SQL:

Select * from `youquan_user_ coupons`where exists (select `id`, `cover`, `cover`, `group_ number`, `group_ cover`from `youquan_ coupons`where `youquan_user_ coupons`.`coupon _ id` = `youquan_ coupons`.`id` and (`group_ ids` = 1) and `youquan_ coupons`.`cards _ at`is null) and (`status` = 1 and `coupons` = 1)

Here you are actually filtering the existing records with exists (). Then go to the next step of the with () query, because it's all filtered through at this time, so with can remove the condition.

Obviously, it's important to distinguish between the two, especially in lists, where you don't have to filter empty data, and it's easy to page.

The above is all the content of the article "how to filter the result set with empty results in the Laravel correlation Model". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!