Laravel - Eloquent - Return Where Related Count Is Greater Than
Stefan Bogdanescu
Founder & Senior Architect · 2026-06-29
# Laravel - Eloquent - Return Where Related Count Is Greater Than
Working with relational data in Laravel, especially when performing complex aggregations across multiple models, often trips up developers. You want to retrieve a list of parent records (e.g., Brands) based on an aggregated metric from their related child records (e.g., the count of Products). While Eloquent provides powerful tools like `whereHas`, sometimes the requirements push us toward more direct database interactions.
This post will diagnose why your initial attempts failed and provide robust, idiomatic solutions for finding the top N related models based on a count, ensuring you get fully hydrated Eloquent models back. Weâll explore both the elegant Eloquent approach and the powerful raw query method.
---
## Diagnosing the Initial Attempts
You correctly identified two common strategies, but they hit roadblocks due to how Eloquent's relationship methods interact with complex constraints:
### Attempt 1: Using `select` and `groupBy`
```php
Product::select('brand', DB::raw('count(brand) as count'))
->groupBy('brand')
->orderBy('count', 'desc')
->take(10)
->get();
```
**The Issue:** This query successfully generates the aggregated data from the database, but it returns a standard collection of generic objects (or arrays), not full `Brand` Eloquent models. You are fetching results from the `products` table context, not the `brands` table context, which is why you see only `Brand` and `Count`, not the full model structure.
### Attempt 2: Using `whereHas` for Filtering
```php
return Brand::whereHas('products', function($q) {
$q->count() > 10;
})->get();
```
**The Issue:** This approach failed with the SQL error (`Column not found`). This often happens when Eloquent constructs a subquery for `whereHas`. While it works well for simple existence checks, trying to embed aggregate functions directly into the constraint check can confuse the underlying join logic or cause issues if the relationship setup isn't perfectly aligned in complex scenarios.
---
## The Correct and Thorough Solution: Using Raw Joins
When you need a true "Top N" result based on an aggregated count across related tables, the most performant and reliable method is to leverage explicit SQL joins and grouping directly via the Query Builder or the underlying database facade. This gives you granular control over exactly which models are returned.
We will join `brands` and `products`, group by the brand, count the products, order them, and then retrieve the resulting brands.
### Implementation with `DB` Facade
This method ensures we return actual `Brand` models, fulfilling the requirement perfectly.
```php
use Illuminate\Support\Facades\DB;
use App\Models\Brand; // Assuming Brand model is in App\Models
$topBrands = Brand::query()
->select('brands.*', DB::raw('COUNT(products.id) as product_count'))
->join('products', 'brands.id', '=', 'products.brand')
->groupBy('brands.id') // Group by the primary key of the brand
->orderBy('product_count', 'desc')
->take(10)
->get();
// The $topBrands collection now contains Brand models, each with an added 'product_count' attribute.
```
### Explanation and Best Practices
1. **`select('brands.*', DB::raw('COUNT(products.id) as product_count'))`**: We select all columns from the `brands` table (`brands.*`) and explicitly calculate the aggregate count using `DB::raw()`. This tells the database exactly what we need: the brand details plus its calculated product count.
2. **`join('products', 'brands.id', '=', 'products.brand')`**: We establish the necessary link between the two tables using an explicit `JOIN`. This is more transparent and often faster than relying solely on Eloquent's implicit relationship loading for complex aggregations.
3. **`groupBy('brands.id')`**: Since we are using an aggregate function (`COUNT`), we must group the results by the non-aggregated columns (in this case, all columns of the `Brand`). Grouping by the primary key is standard practice.
4. **`orderBy('product_count', 'desc')` and `take(10)`**: Standard sorting and limiting to get only the top 10 brands with the most products.
This technique demonstrates that while Eloquent relationships are fantastic for fetching related data, complex aggregations often require stepping down to the Query Builder layer to achieve the optimal performance and structure required. This level of control is key when building high-performance data retrieval systems in Laravel. For deeper insights into optimizing your database queries within the Laravel ecosystem, exploring advanced features shown on [laravelcompany.com](https://laravelcompany.com) is highly recommended.
## Conclusion
Finding related entities based on aggregated metrics is a common task in application development. When simple filtering fails and you need complex ordering and aggregation across joins, pivot to using explicit database operations via the `DB` facade. By mastering the combination of `join`, `groupBy`, and `select` with aggregate functions, you gain complete control over your data retrieval, ensuring accuracy and performance. For this specific scenario, the raw query approach provides the most direct path to retrieving fully hydrated Eloquent models based on their associated counts.