Eloquent Relationships for Complex Queries: Counting by Last Status in Laravel

======================================================

As a developer, you often find yourself dealing with complex queries that require joining multiple tables and applying various filters. In this article, we will explore how to use Eloquent relationships to simplify such queries. We’ll focus on counting the occurrences of each status from the last occurrence in a table hierarchy.

Background: Table Structure and Relationships

Let’s take a look at the provided table structure:

p_table
id
name

----------------
history_p_table
id
p_id (many-to-one to p_table)
status_id (many-to-one to status_table)
date_history
order_history

------------
status_table
------------
id
name

The history_p_table has a many-to-one relationship with both p_table and status_table. This means that each history record is linked to its corresponding packet (p_id) and status.

The Challenge: Counting by Last Status

We want to count the occurrences of each status from the last occurrence in the table hierarchy. We can break this down into two main parts:

Retrieve the latest status for each packet.
Count the occurrences of each status.

Solution: Using Eloquent Relationships and GroupBy Clause

To solve this problem, we will use Eloquent relationships to retrieve the required data and then apply a groupby clause to count the occurrences of each status.

// Get the latest status for each packet using relationship
$p ->latestStatus();

// Count the occurrences of each status
$counts = $p-> latestStatus()->groupBy(function ($status) {
    return $status->name;
})->count();

However, we need to ensure that we retrieve the correct data from the history_p_table and join it with the relevant tables. We can achieve this using Eloquent’s relationship methods.

Example Code

Let’s start by defining the models:

// App\Models\P.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class P extends Model
{
    public function statusNow(): HasMany
    {
        return $this->hasMany(HistoryP::class);
    }
}

// App\Models\HistoryP.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class HistoryP extends Model
{
    public function status(): BelongsTo
    {
        return $this->belongsTo(Status::class);
    }

    public function p(): BelongsTo
    {
        return $this->belongsTo(P::class);
    }
}

Now, let’s retrieve the required data:

// Get Packet counts by last status
$p ->latestStatus()->groupBy(function ($status) {
    return $status->name;
})->count();

This will give us an array with the count of each status.

Explanation

In the above code:

We define two Eloquent models: P and HistoryP.
The P model has a many-to-one relationship with HistoryP, which in turn has a many-to-one relationship with Status. This allows us to retrieve the latest status for each packet using the latestStatus() method.
We then group the data by the status name and count the occurrences using the groupBy() and count() methods.

Alternative Solution: Using Raw SQL Query

If you prefer not to use Eloquent relationships, you can use a raw SQL query to achieve the same result. Here’s an example:

// Get Packet counts by last status (raw SQL)
$p->statusNow()->selectRaw("status.name as status_name")
            ->groupBy('status_name')
            ->count();

This will give us the same result as the Eloquent solution.

Conclusion

In this article, we explored how to use Eloquent relationships to simplify complex queries. We focused on counting the occurrences of each status from the last occurrence in a table hierarchy. We also provided an alternative solution using raw SQL query. By following these examples and understanding how to work with Eloquent relationships, you can efficiently manage your data and improve your code’s performance.