Laravel orderByRaw正在删除联合查询的结果。

Question

我在一个具有"meta_competitions“和"primary_events”的项目中运行这个查询，该项目将合并成一个结果，在这个结果中，具有当前primary_event的primary_event_id的primary_event_meta_competitions不与其重叠。

在查询本身，一切都如预期的那样工作，我们正在得到我们想要的结果。但是，当我们添加orderByRaw()时，除了一个结果之外，所有东西都会从meta_competitions中删除。

当我在纯sql中运行"->toSql()“而不是查询的->get()时，我尝试运行所获得的查询，并且所有操作都与预期的一样。

$metaCompetitions = DB::table('meta_competitions')
    ->select('meta_competitions.name as name', 'sports.name as sport', 'meta_competitions.id', 'meta_competitions.country')
    ->where('meta_competitions.name', 'LIKE', "%{$string}%")
    ->whereIn('meta_competitions.sport_id', [1, 3, 4, 9])
    ->join('sports', 'sports.id', '=', 'meta_competitions.sport_id');

$leagues = DB::table('primary_events')

    ->select('primary_events.name as name', 'sports.name as sport', 'primary_events.id', 'primary_events.country')
    ->where('primary_events.name', 'LIKE', "%{$string}%")
    ->whereIn('primary_events.sport_id', [1, 3, 4, 9])
    ->whereNull('primary_event_meta_competitions.primary_event_id')
    ->leftJoin('primary_event_meta_competitions', 'primary_event_meta_competitions.primary_event_id', '=', 'primary_events.id')
    ->leftJoin('sports', 'sports.id', '=', 'primary_events.sport_id')

    ->union($metaCompetitions)

    ->orderByRaw(
        'CASE
            WHEN name = ? THEN 1
            WHEN name LIKE ? THEN 2
            WHEN name LIKE ? THEN 4
            ELSE 3
          END', [$string, "{$string}%", "%{$string}"]
    )
    ->get();

    dd($leagues);

我从->toSql()获得并对我有用的查询如下：

(select `primary_events`.`name` as `name`, `sports`.`name` as `sport`, `primary_events`.`id`, `primary_events`.`country` from `primary_events` 
left join `primary_event_meta_competitions` on `primary_event_meta_competitions`.`primary_event_id` = `primary_events`.`id` 
left join `sports` on `sports`.`id` = `primary_events`.`sport_id` 
where `primary_events`.`name` LIKE '%Allsvenskan%' and `primary_events`.`sport_id` in (1, 3, 4, 9) and `primary_event_meta_competitions`.`primary_event_id` is null) 
union (select `meta_competitions`.`name` as `name`, `sports`.`name` as `sport`, `meta_competitions`.`id`, `meta_competitions`.`country` from `meta_competitions` 
inner join `sports` on `sports`.`id` = `meta_competitions`.`sport_id` 
where `meta_competitions`.`name` LIKE '%Allsvenskan%' and `meta_competitions`.`sport_id` in (1, 3, 4, 9)) 
order by CASE
                WHEN name = 'Allsvenskan' THEN 1
                WHEN name LIKE 'Allsvenskan%' THEN 2
                WHEN name LIKE '%Allsvenskan' THEN 4
                ELSE 3
              END

我的桌子看起来像：

meta_competitions
id:name:sport_id:country

primary_events
id:name:sport_id:country

sports
id:name

primary_event_meta_competitions
primary_event_id:meta_competition_id

Answer 1

这是个奇怪的问题。当我不使用orderByRaw，而是对一个自定义列使用selectRaw并对该列进行排序时，一切都按预期进行了工作。

为什么这一切都不一样？

    $metaCompetitions = DB::table('meta_competitions')
        ->select('meta_competitions.name as name', 'sports.name as sport', 'meta_competitions.id', 'meta_competitions.country')
        ->selectRaw('CASE
                WHEN meta_competitions.name = ? THEN 1
                WHEN meta_competitions.name LIKE ? THEN 2
                WHEN meta_competitions.name LIKE ? THEN 4
                ELSE 3
              END AS order_col', [$string, "$string%", "%$string"] )
        ->selectRaw("'meta_competition' AS type")
        ->where('meta_competitions.name', 'LIKE', "%{$string}%")
        ->whereIn('meta_competitions.sport_id', [1, 3, 4, 9])
        ->join('sports', 'sports.id', '=', 'meta_competitions.sport_id');

    $leagues = DB::table('primary_events')

        ->select('primary_events.name as name', 'sports.name as sport', 'primary_events.id', 'primary_events.country')
        ->selectRaw('CASE
                WHEN primary_events.name = ? THEN 1
                WHEN primary_events.name LIKE ? THEN 2
                WHEN primary_events.name LIKE ? THEN 4
                ELSE 3
              END AS order_col', [$string, "$string%", "%$string"] )
        ->selectRaw("'league' AS type")
        ->where('primary_events.name', 'LIKE', "%{$string}%")
        ->whereIn('primary_events.sport_id', [1, 3, 4, 9])
        ->whereNull('primary_event_meta_competitions.primary_event_id')
        ->leftJoin('primary_event_meta_competitions', 'primary_event_meta_competitions.primary_event_id', '=', 'primary_events.id')
        ->leftJoin('sports', 'sports.id', '=', 'primary_events.sport_id')

        ->union($metaCompetitions)

        ->orderBy('order_col')

        ->get();