CODEWITHSUNDEEP
mysqllaraveleloquentlaravel-query-builder
crabbly
crabbly

Reputation: 5186

Eloquent query to return items ordered by second level relation

I'm trying to build a query which will return records of the item, with its relations. However, it needs to be ordered by a second level relation. And, it also needs to be paginated.

Here are the models and relations:

class FirstModel extends Model
{
    public function secondModel()
    {
        return $this->hasMany(SecondModel::class);
    }
}

class SecondModel extends Model
{
    public function firstModel()
    {
        return $this->belongsTo(FirstModel::class);
    }

    public function thirdModel()
    {
        return $this->belongsToMany(ThirdModel::class);
    }
}

class ThirdModel extends Model
{
    public function secondModel()
    {
        return $this->belongsToMany(SecondModel::class);
    }
}

First model has a one to many relation with second model (second model table has a field for first model id).

Second model has a many to many relation with Third model (using a pivot table with first model id and second model id).

The desired return would be a collection of FirstModel items ordered by the ThirdModel id.

How could I accomplish this query using Eloquent or Laravel's DB Query builder?

Thank you.

Upvotes: 3

Views: 240

Answers (1)

fubar
fubar

Reputation: 17388

To achieve what you've described, you're going to have to use the query builder and perform joins across the tables.

When doing this, you'll also need to be vigilant about models using soft deletes and handle checks for deleted models yourself using whereNull('deleted_at') clauses. I've commented these out in my example.

$firsts = FirstModel::select('first_models.*')
    ->join('second_models', 'first_models.id', '=', 'second_models.first_model_id')
    ->join('second_models_third_models', 'second_models.id', '=', 'second_models_third_models.second_model_id')
    ->join('third_models', 'third_models.id', '=', 'second_models_third_models.third_model_id')
    //->whereNull('second_models.deleted_at')
    //->whereNull('third_models.deleted_at')
    ->orderBy('third_models.id', 'asc')
    ->groupBy('first_models.id')
    ->paginate(10);

I would be inclined to move this query into a query scope to keep your controllers tidy and to ease future re-use.

Upvotes: 2

Related Questions