Laravel query orderBy has 'media' using Spatie\MediaLibrary

Question

Im currently using the Spatie\MediaLibrary for my reviews model.

I got a query getting all reviews but im trying to show reviews that has images first using orderBy.

app\Models\Product.php Not working

public function reviews(): \Illuminate\Database\Eloquent\Relations\HasMany
{
    return $this->hasMany(Review::class)->orderBy('media');
}

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'media' in 'order clause'

Using has 'media' or doesnthave 'media' works. But I would like in the 1 query.

public function activeImageReviews(): \Illuminate\Database\Eloquent\Relations\HasMany
{
    return $this->hasMany(Review::class)->has('media');

Answer 1

If there exists a column called 'media' in the database you mig try:

public function reviews(): \Illuminate\Database\Eloquent\Relations\HasMany
{
  return $this->hasMany(Review::class)->orderByRaw('CASE WHEN `media` IS NULL THEN 0 ELSE 1 END ASC');
}

Answer 2

Unknown column 'media' implies that the column is not created in the table, make sure your migration is properly created.

In other case, if your media is from a normalized, then your approach is supposed to be creating the relation between your Review model and your Media model then making the relation using with()

return $this->hasMany(Review::class)->with('media')->orderBy('media.column');

---

Edit:

Since it is File-based only, then it is recommended to sort the collection after fetching as by documentation:

                    // sortBy
$sorted = $collection->sortByDesc(function(Model $item) {
    $media = ...;
    return $item->has($media);
});