雄辩关系：如何通过最新帖子订购主题？

Question

I created a table like this (table name: supports):

$table->increments('id');
$table->integer('user_id')->unsigned()->nullable();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

$table->integer('parent_id')->unsigned()->nullable();
$table->foreign('parent_id')->references('id')->on('supports')->onDelete('cascade');

$table->text('body');
$table->timestamps();

in support model I do this:

public function supports()
{
    return $this->hasMany(Support::class , 'parent_id' , 'id');
}

public function latestSupport()
{
    return $this->hasOne(Support::class,'parent_id','id')->orderBy('created_at', 'desc')->latest();
}

And Finally I try call this function (in model) in controller:

public function scopeSearch($query, $input)
{
    $query->where('parent_id',null);
    $query->with('latestSupport');

    return $query->latest();
 }

It return list of root (topics) ...But It couldn't sort roots by newest post for each root.

Could you please tell me the wrong?

---

Edit: in controller:

$supports = Support::Search($input)->paginate(20);

Answer 1

I think what you are looking for is the following, where we sort on the related created_at instead of the root one. To achieve this, we need to perform a subquery (or perform a join).

public function scopeSearch($query, $input)
{
    $query->with('latestSupport');
    $query->whereNull('parent_id');

    $query->select('supports.*', \DB::raw(
        '(SELECT MAX(s2.created_at) FROM supports as s2 WHERE supports.id = s2.parent_id) as sort'
    ));
    $query->orderBy('sort');

    return $query;
}

I also replaced where('parent_id', null) by whereNull('parent_id') to utilize the native SQL grammar for this.