2 minutes April 1, 2024

Sorting Records with hasMany Relationship in Laravel

When working with Laravel and its Eloquent ORM, sorting records based on related table columns can sometimes be challenging, especially when dealing with a hasMany relationship. This article aims to provide a solution for sorting records efficiently in such scenarios.

Problem

Consider a situation where you have two models, Asset and AssetHistory, with a hasMany relationship between them. You want to fetch assets along with their latest asset history record and sort them based on a column in the AssetHistory table, such as created_at.

Solution

Step 1: Define Relationships

As a first step you need to ensure that you have defined the relationships correctly in your models.

```php

// Asset.php

namespace AppModels;



use IlluminateDatabaseEloquentModel;



class Asset extends Model

{

    /**

     * Get the asset histories for the asset.

     */

    public function assetHistories()

    {

        return $this->hasMany(AssetHistory::class);

    }

}

```php

// Asset.php

namespace AppModels;

use IlluminateDatabaseEloquentModel;

class Asset extends Model

{

/**

* Get the asset histories for the asset.

public function assetHistories()

{

return $this->hasMany(AssetHistory::class);

}

   
 
 /**
 * Insert your code here
 */
     1
2
3
  /**
 * Insert your code here
 */  
 
 
 
Step 2: Fetch Assets with Latest Asset History and Sort

Now, let's write the code to fetch assets along with their latest asset history records and sort them based on the created_at column in the AssetHistory table.

   
 
 ```php

use AppModelsAsset;

use IlluminateSupportFacadesDB;



// Fetch assets with latest asset history

$assets = Asset::with(['assetHistories' => function ($query) {

// Subquery to get the latest asset history for each asset

$query->select(DB::raw('MAX(id) as max_id'))->groupBy('asset_id');

}])

->leftJoin('asset_histories', function ($join) {

// Join with the latest asset history records

$join->on('asset_histories.asset_id', '=', 'assets.id')

->on('asset_histories.id', '=', DB::raw('(SELECT max_id FROM asset_histories)'));

})

->orderBy('asset_histories.created_at', 'desc') // Sort by created_at in asset_histories

->get();



// Now $assets contains assets with their latest asset history records sorted by created_at

```
     1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
  ```php
 
use AppModelsAsset;
 
use IlluminateSupportFacadesDB;
 
 
 
// Fetch assets with latest asset history
 
$assets = Asset::with(['assetHistories' => function ($query) {
 
// Subquery to get the latest asset history for each asset
 
$query->select(DB::raw('MAX(id) as max_id'))->groupBy('asset_id');
 
}])
 
->leftJoin('asset_histories', function ($join) {
 
// Join with the latest asset history records
 
$join->on('asset_histories.asset_id', '=', 'assets.id')
 
->on('asset_histories.id', '=', DB::raw('(SELECT max_id FROM asset_histories)'));
 
})
 
->orderBy('asset_histories.created_at', 'desc') // Sort by created_at in asset_histories
 
->get();
 
 
 
// Now $assets contains assets with their latest asset history records sorted by created_at
 
```  
 
 
 
 
Explanation

We use the with method to eager load the assetHistories relationship with a callback function.
Within the callback function, we select the maximum id for each asset_id in the asset_histories table using a subquery.
Then, we perform a left join with asset_histories where we match the asset_id and the id obtained from the subquery, ensuring we get the latest asset history record for each asset.
Finally, we order the results based on the created_at` column of the latest asset history record.

Conclusion

By following the steps outlined above, Laravel development agencies can efficiently fetch assets with their latest asset history records. Laravel developers can sort them based on related table columns even when dealing with a has many relationships in Laravel. This approach ensures accuracy and performance in sorting records based on related data, making it a valuable technique for Laravel development projects.

Recent help desk articles

Greetings! I'm Aneesh Sreedharan, CEO of 2Hats Logic Solutions. At 2Hats Logic Solutions, we are dedicated to providing technical expertise and resolving your concerns in the world of technology. Our blog page serves as a resource where we share insights and experiences, offering valuable perspectives on your queries.