How to sort parent data based on child column detial in laravel 5.4?

Question

I have 3 way relationship firstl i have get code like this in a controller

Trial::with('subjects')->where('source_id', $sourceId)->get()->toArray()

Now I want to sort subject.reactions on desc order of subject.reactions.accounts.nb_followers column. I tried to use orderby on relationship but it does not work because it sorting account indsted on reactions. I want to sort reaction based on value of "nb_followes" column present inside account table.

Trail Model

class Trial extends Model
{
    use HasFactory;
    public $table = 'trials';

    public function subjects()
    {
        return $this->hasMany(Subject::class, 'trial_id')->with(['reactions', 'news'])->withCount('reactions');
    }
  } 

Subject Model

class Subject extends Model
{
    use HasFactory;
    public $table = 'subjects';

    public function reactions()
    {
        return $this->hasMany(Reaction::class, 'subject_id', 'id')->with(['accounts' => function ($q) {$q->orderBy('nb_followers', 'DESC');}])->where('twitter_error', 0)->where('active', 1)->orderby('key_reaction', 'DESC');
    }

    public function news()
    {
        return $this->hasone(News::class, 'id', 'news_item_id');
    }

Reaction Model

class Reaction extends Model
{
    use HasFactory;

    public $table = 'reactions';

    public function accounts()
    {
        return $this->belongsTo(Account::class, 'account_id', 'id')->where('name', '!=', '');
    }

Thank you in Advance.

I want to sort reactions based on account table's column yes i cant use simple eloquent query because it will not create a structure that i want so that's why i created these relationships.

Answer 1

1. first you need to loop over trails using map method
2. second you need to use transform method to transform your subject in sorted manner
3. as show in Subject model you need sort reaction by key_reaction feild

$trails = ViewTrial::with('subjects')->where('source_id', $sourceId)->get();

$trails->map(function($trails){
        return $trails->subjects = $trails->subjects->transform(function (&$subject) {
           return [
                      "column" => $subject->column,
                      "reactions" => $subject->reactions->sortByDesc(function ($reactions) {
                                         return $reactions['accounts']['nb_followers'];
                                    })->sortByDesc('key_reaction')->values()->all()    
                   ]
                })->values();
            });

return $trails->toArray();