Eloquent Multiple Table Join With Filters in Laravel 11 With Code Examples

In this new guide or post whatever you may name it, we are going to understand and implementing multiple table joins with filters in Laravel 11’s Eloquent ORM can be challenging as well as game-changer too; for devs in 2024—specially while working on a complex database queries. Like, I have been working with Laravel for several years now, and I can confidently say that mastering this technique will significantly enhance your ability to retrieve and manipulate data efficiently. So, when we are dealt with multiple tables in a relational database, it is often necessary to combine data from these tables based on certain conditions. This is where joins come into play. Eloquent, Laravel 11’s powerful ORM, would give us a fluent interface to perform these joins, making it easier for developers to write expressive and readable code. Eloquent, the ORM (Object-Relational Mapping) system in Laravel 11, offers powerful tools for working with databases. When are developing complex applications, we often need to retrieve data from multiple related tables while applying various filters. This is where Eloquent’s join capabilities truly shine.

Let us begin by setting up a realistic scenario. Imagine we are building an eCommerce platform with the following database structure:

#1 Products table
#2 Categories table
#3 Orders table
#4 OrderItems table
#5 Users table

In this system, a product belongs to a category, an order belongs to a user, and order items connect products to orders. We would go on to explore how to perform complex queries across these tables using Eloquent’s join and filter capabilities.
First, let us define our models and their relationships:

// Product.php
class Product extends Model
{
    public function category()
    {
        return $this->belongsTo(Category::class);
    }

    public function orderItems()
    {
        return $this->hasMany(OrderItem::class);
    }
}

// Category.php
class Category extends Model
{
    public function products()
    {
        return $this->hasMany(Product::class);
    }
}

// Order.php
class Order extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function orderItems()
    {
        return $this->hasMany(OrderItem::class);
    }
}

// OrderItem.php
class OrderItem extends Model
{
    public function order()
    {
        return $this->belongsTo(Order::class);
    }

    public function product()
    {
        return $this->belongsTo(Product::class);
    }
}

// User.php
class User extends Model
{
    public function orders()
    {
        return $this->hasMany(Order::class);
    }
}

Example 1 – Retrieving all products in a specific category that have been ordered in the last month

Now that we have established our model relationships, let us explore some complex queries using Eloquent’s join and filter capabilities.

$lastMonth = now()->subMonth();

$popularProducts = Product::join('categories', 'products.category_id', '=', 'categories.id')
    ->join('order_items', 'products.id', '=', 'order_items.product_id')
    ->join('orders', 'order_items.order_id', '=', 'orders.id')
    ->where('categories.name', 'Electronics')
    ->where('orders.created_at', '>=', $lastMonth)
    ->select('products.*')
    ->distinct()
    ->get();

Example 2 – Retrieving products with their total sales amount in the last quarter, for a specific category

In this query, we are joining the products table with categories, order_items, and orders tables. We are filtering for products in the ‘Electronics’ category that have been ordered within the last month. The distinct() method ensures we do not have duplicate product entries in our result set.

$lastQuarter = now()->subQuarters(1);

$productSales = Product::join('categories', 'products.category_id', '=', 'categories.id')
    ->join('order_items', 'products.id', '=', 'order_items.product_id')
    ->join('orders', 'order_items.order_id', '=', 'orders.id')
    ->where('categories.name', 'Clothing')
    ->where('orders.created_at', '>=', $lastQuarter)
    ->select('products.*', DB::raw('SUM(order_items.quantity * products.price) as total_sales'))
    ->groupBy('products.id')
    ->orderBy('total_sales', 'desc')
    ->get();

Example 3 – Finding users who have purchased products from multiple categories in a single order

This query joins the same tables as before but calculates the total sales amount for each product in the ‘Clothing’ category over the last quarter. We are using DB::raw() to perform the calculation within the SQL query itself.

$multiCategoryBuyers = User::join('orders', 'users.id', '=', 'orders.user_id')
    ->join('order_items', 'orders.id', '=', 'order_items.order_id')
    ->join('products', 'order_items.product_id', '=', 'products.id')
    ->join('categories', 'products.category_id', '=', 'categories.id')
    ->groupBy('users.id', 'orders.id')
    ->havingRaw('COUNT(DISTINCT categories.id) > 1')
    ->select('users.*')
    ->distinct()
    ->get();

Example 4 – Using whereHas() for efficient filtering

This complex query finds users who have placed orders containing products from more than one category. We are using havingRaw() to filter based on the count of distinct categories per order. While these join queries are powerful, they might not always be the most efficient approach, especially for larger datasets. Eloquent provides alternative methods that can sometimes be more performant:

$popularProducts = Product::whereHas('category', function($query) {
        $query->where('name', 'Electronics');
    })
    ->whereHas('orderItems.order', function($query) {
        $query->where('created_at', '>=', now()->subMonth());
    })
    ->get();

Example 5 – Using withCount() and having() for aggregations

This query achieves the same result as Example 1 but uses Eloquent’s whereHas() method instead of joins. This approach can be more efficient as it leverages Eloquent’s lazy loading capabilities.

$productSales = Product::withCount(['orderItems as total_quantity' => function($query) {
        $query->whereHas('order', function($subQuery) {
            $subQuery->where('created_at', '>=', now()->subQuarters(1));
        });
    }])
    ->having('total_quantity', '>', 0)
    ->orderBy('total_quantity', 'desc')
    ->get();

This query retrieves products and their total sold quantity in the last quarter, ordered by the quantity sold. It uses withCount() to add a count of related models as a column in the result set. When working with Eloquent joins and filters, it is crucial to consider query performance. For very complex queries, you might need to use the DB facade or even raw SQL queries. However, Eloquent’s methods often provide a good balance between readability and performance. Remember, as your application grows, you might want to encapsulate these complex queries into query scopes or dedicated repository classes to keep your code clean and maintainable.