How to sort by condition in Laravel?

Question

Laravel

Hi all!
Please tell me how to sort by condition in Laravel?
The task is this:
there is a field with a date in the table, if the date is greater than the current date, then the function should work with ->orderBy('top_date', 'DESC')
Here is the code:

return User::where('is_deleted', '0')
      ->with(['langs', 'categories'])
      ->where('is_published', true)
      ->where('subscription', '>=', now())
      ->orderBy('balls', 'DESC')
      ->filter($filters)
      ->paginate(15);

Answer 1

You need to make a request like this
On Laravel it would look something like this

return User::where('is_deleted', '0')
      ->with(['langs', 'categories'])
      ->where('is_published', true)
      ->where('subscription', '>=', now())
      ->orderByRaw('CASE WHEN top_date > NOW() THEN top_date END DESC, balls DESC')
      ->filter($filters)
      ->paginate(15);

Answer 2

I will offer a crutch, maybe more experienced comrades have a normal solution ...

$users = User::where('is_deleted', '0')
            ->with(['langs', 'categories'])
            ->where('is_published', true)
            ->where('subscription', '>=', now())
            ->orderBy('balls', 'DESC')
            ->filter($filters)
            ->paginate(15);
        foreach ($users as $user) {
            if ($user->top_date > Carbon::now()) {
                $users = $users->sort(function ($user) {
                    return $user->top_date;
                });
                break;
            }
        }
        return $users;

Answer 3

I would suggest

// подготовка запроса 
$query =  User::where('is_deleted', '0')
      ->with(['langs', 'categories'])
      ->where('is_published', true)
      ->where('subscription', '>=', now())
      ->orderBy('balls', 'DESC');

// Проверка на условие 
if ($query->where('top_date', '>=', now())->count()) - будет лишний запрос в базу и если не 0 то добавляем к запросу сортировку.
     {$query = $query->orderBy('top_date', 'DESC');}

// выполнение Запроса.
$query = $query->filter($filters)
                          ->paginate(15);

In this case, the load is transferred to the SQL server.