OrderByRaw is removing results in union query

I am running this query in a project where I am having "meta_competitions" and "primary_events" that is going to merge together into a result where they do not overlap by primary_event_meta_competitions having the primary_event_id of the current primary_event.

Everything is working as exprected in the query itself, we are getting the results we want. But when we add the orderByRaw() everything except one result is removed of the meta_competitions.

$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);

jimmitjoo

8 years ago

Best Answer

Level 12

This is really interesting! When I instead moved and used it as a custom column in the results everything is working. Is this possibly a bug in Laravels orderByRaw?

When I removed the orderByRaw and instead used it in a selectRaw that looked like this, it works as expected! Why is it different at all?

$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();

1 like

Please or to participate in this conversation.