如何在Eloquent中通过外表中的列聚合与多对多关系进行分组？

Question

I am starting to get headaches over this so I thought I just post it here. I have two tables that are related through a pivot table (as it is a many-to-many relationship). I use Laravel and Eloquent (but general help on how to achieve this with normal SQL queries is also highly appreciated).
I want to order the first table based a column of the second one but the column needs to be "aggregated" for this.

Example with Cars that are shared by many drivers and can have different colors:

Car-Table: [id, color]  
Driver-Table: [id, name]  
Car.Driver-Table: [car_id, driver_id]

I need a query that gets all drivers that only drive red cars and then all that don't drive red cars.
I have to use a query because I'll maybe do other things (like filtering) on this query afterwards and want to paginate in the end.
I already use queries that get either one of the two groups. They look like this: In the Driver model:

public function redCars() {
    return $this->cars()->where('color', 'red');
}

public function otherColoredCars() {
    return $this->cars()->where('color', '<>', 'red');
}

And then in somewhere in a controller:

$driversWithOnlyRedCars = Driver::whereDoesntHave('otherColoredCars')->get();
$driversWithoutRedCars = Driver::whereDoesntHave('redCars')->get();

Is there a way to combine these two?
Maybe I am just thinking completely wrong here.

Update for clarification:
Basically I would need something like this (ot any other way that would lead to the same outcome)

$driversWithOnlyRedCars->addTemporaryColumn('order_column', 0); // Create temporary column with value 0
$driversWithoutRedCars->addTemporaryColumn('order_column', 1);
$combinedQuery = $driversWithOnlyRedCars->combineWith($driversWithoutRedCars); // Somehow combine both queries
$orderedQuery = $combinedQuery->orderBy('order_colum');
$results = $combinedQuery->get();

Update 2
I think, I found out how to get near my goal with raw queries.
Would be something like this:

$a = DB::table(DB::raw("(
  SELECT id, 0 as ordering
  FROM drivers
  WHERE EXISTS (
    SELECT * FROM cars
    LEFT JOIN driver_car ON car.id = driver_car.car_id
    WHERE driver.id = driver_car.driver_id
    AND cars.color = 'red'
  )
) as only_red_cars"));

$b = DB::table(DB::raw("(
  SELECT id, 1 as ordering
  FROM drivers
  WHERE EXISTS (
    SELECT * FROM cars
    LEFT JOIN driver_car ON car.id = driver_car.car_id
    WHERE driver.id = driver_car.driver_id
    AND cars.color <> 'red'
  )
) as no_red_cars"));

$orderedQuery = $a->union($b)->orderBy('ordering');

Now the problem is that I need the models ordered like this and paginated in the end so this is not really an answer to my question. I tried to convert this back to models but I didn't succeed yet. What I tried:

$queriedIds = array_column($orderedQuery->get()->toArray(), 'id');
$orderedModels = Driver::orderByRaw('(FIND_IN_SET(drivers.id, "' .  implode(',', $queriedIds) . '"))');

But looks like FIND_IN_SET only allows for a column of the table as second parameter. Is there another way to get the Models in the right order out of the ordered union query?

Answer 1

You can use a UNION query:

$driversWithOnlyRedCars = Driver::select('*', DB::raw('0 as ordering'))
    ->whereDoesntHave('otherColoredCars');

$driversWithoutRedCars = Driver::select('*', DB::raw('1 as ordering'))
    ->whereDoesntHave('redCars');

$drivers = $driversWithOnlyRedCars->union($driversWithoutRedCars)
    ->orderBy('ordering')
    ->orderBy('') // TODO
    ->paginate();

How do you want drivers with the same ordering to be ordered? You should add a second ORDER BY clause to get a consistent order every time you execute the query.