Order by column value

user_10139010 0
Viewed 194

I have a search feature which returns films and orders them by a timestamp, i have a filter called status with string values as consts which are online and offline I want this filter to display profiles with both results but all the online ones first and offline at the bottom

Can you order by a column string value so it only returns films that are online at the top and offline at the bottom? something like below

->orderBy('status', 'online', desc)

Query

Below is my current query which orders by a timestamp would adding another orderBy be enough for a solution?

 Film::select('films.*')
        ->when($filters->input('status'), function ($query, $status) {
               $query->where('status', $status);
            }
        })
        ->orderBy('films.updated_at', 'desc')
        ->distinct()
        ->get();
eloquent laravel
2 Answers

You can invoke orderBy() as many times as you need it. 

 Film::select('films.*')
            ->when($filters->input('status'), function ($query, $status) {
                if ($status !== FilmStatus::OFFLINE) {
                    $query->where('status', $status);
                }
            })
            ->orderBy('films.updated_at', 'desc')
            ->orderBy('status', 'ASC')
        ->distinct()
        ->get();
user_57235240

You can use conditional ordering like so:

Film::select('films.*')
    ->when($filters->input('status'), function ($query, $status) {
            $query->where('status', $status);
    })
    ->orderBy(\DB::raw("
        CASE WHEN status = 'online' THEN 1 ELSE 0 END DESC
    "))
    ->distinct()
    ->get();

In above query if status is online it will be on top and if status is other it will be below. Hope this helps you

user_95074020
Related