How to build a query with 3 sort levels?

Question

MySQL

If id is not in the get-parameter (string) or in the session (array), then we display all available colors by popularity. If there is an id in the get parameter, then that color should come first. If there is an id in the get-parameter and there is a lot of id in the session, then the first one should be the color by the get-parameter, then the colors from the session array, and only then all the other colors. At the same time, all colors must be sorted by popularity and be available.

Now I only check for the presence of the get-parameter.

public function index(Request $request)
{
    $colors_ids = $request->session()->get('colors_ids');

    $this->validate($request, [
        'color' => 'numeric',
    ]);

    $colors = Color::where()
                   ->orderByRaw(sprintf("CASE id WHEN %d THEN 1 ELSE 2 END", $request->get('color')))
                   ->orderBy("popularity", "desc")
                   ->get();

    return view('color.index', compact('colors'));
}

Answer 1

Here is a variant of the SQL query:

select *
from colors
order by 
  id = 3 desc, -- 3 in get
  id in (5, 6) desc, -- 5 and 6 in session 
  popularity asc;

MariaDB fiddle

Answer 2

It seems you need a UNION

Answer 3

ORDER BY `color` != IFNULL(:color, 0),
         FIND_IN_SET(`color`, :colors) != 0,
         `popularity`