Eloquent query to return items ordered by a second level relationship
I am trying to create a query that will return the records of an item with its relationships. However, it needs to be streamlined in relation to the second level. Moreover, it also needs to be paginated.
Here are the models and relationships:
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);
}
}
The first model has a one-to-many relationship with the second model (the second model table has a field for the first model ID).
The second model has many, many relationships with the third model (using a pivot table with the first model ID and the second model ID).
The desired income will be a set of FirstModels, ordered by the ThirdModel ID.
How could I accomplish this query using Eloquent or Laravel DB's query builder?
Thank.
source to share
To achieve what you described, you will have to use the Query Builder and do table joins.
That being said, you also need to be vigilant about models using soft deletes and perform checks on deleted models yourself using suggestions whereNull('deleted_at')
. I commented on this 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 tend to move this query to the query area to keep your controllers neat and to make it easier to reuse in the future.
source to share