How to Select Not Null Data Using whereHas in Laravel

Learn how to use `whereHas` in Laravel Eloquent queries to select non-null data by checking associated relations effectively.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to select not null datas using whereHas in LARAVEL

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Select Not Null Data Using whereHas in Laravel

When building applications with Laravel, you might often face the challenge of filtering data that meets specific criteria. A common scenario involves selecting records that have associated records where a certain field is not null. For instance, if you want to retrieve cities that have shops, and those shops must have employees associated with them, you can achieve that using Laravel's powerful Eloquent ORM.

In this guide, we'll explore how to filter cities based on their relationships with shops and ensure that only the shops with employees are included in your results. Let’s dive into the specifics!

The Challenge

In our example, we have three database tables: countries, cities, and shops. Each country can have multiple cities, and each city can have multiple shops. Additionally, shops can have associated employees.

Objective

We want to display only those cities that have shops, and those shops must have at least one employee.

Here’s the scenario:

We have cities, some of which may have no shops (or empty shops) and some that do.

All we want is to showcase the cities that actually have shops with employees.

For example, given this data structure:

[[See Video to Reveal this Text or Code Snippet]]

In the above structure, the city North Michealbury has no shops, while Millsmouth has a shop with employees.

The Solution

To solve this problem, we can use the whereHas method in Laravel. This method is used to filter results based on the existence of related models.

Here's how you could write the robust Eloquent query to achieve the desired filtering:

Query Structure

[[See Video to Reveal this Text or Code Snippet]]

Explanation Breakdown

Eager Loading with with:

The with method allows you to specify relationships to load alongside the results. In this case, we are loading both cities and their corresponding shops.

Additionally, we use the withCount method to get the count of employees directly related to shops.

Filtering with whereHas:

The outer whereHas checks if any of the cities associated with the country have any shops.

The inner whereHas checks if those shops actually have employees.

The Final Call:

The get() method retrieves all the filtered results as a collection.

Conclusion

By effectively using whereHas in your Laravel queries, you can filter data based on relationships and ensure you only retrieve meaningful records. Remember, the key takeaway is to understand how to traverse relationships and apply constraints that meet your data needs.

Whether you are displaying cities, countries, or any other hierarchical data structure, mastering these techniques will significantly enhance the efficiency of your data retrieval processes.

Now go ahead, leverage the power of Eloquent in Laravel to streamline your data handling!