doushan1863
2019-06-25 14:29
浏览 165
已采纳

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

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?

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dtcn30461 2019-07-05 14:44
    已采纳

    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.

    评论
    解决 无用
    打赏 举报
查看更多回答(1条)

相关推荐 更多相似问题