dimanche 26 janvier 2020

Order by CASE with different column values in Laravel does not work

I am trying to build a laravel query which should order values by more columns: first of all, by hour and this works and also by values in a column named "competition_type" in this way: 'ATP - SINGLES', 'WTA - SINGLES', 'CHALLENGER MEN - SINGLES', 'CHALLENGER WOMEN - SINGLES', 'ITF MEN - SINGLES', 'ITF WOMEN - SINGLES' and I tried this query, but it does work...I tried several approaches and this is my last one:

$matches = Match::select()
           ->where('date', $date)->where('pick_score', '<>', '0');

    $matches = Match::where('date', $date)
                ->where('pick_score', '<>', '0');

    $matches = Match::select("CASE 
                                WHEN competition_type like '%ATP - SINGLES%' then 1
                                WHEN competition_type like '%WTA - SINGLES%' then 2 
                                WHEN competition_type like '%CHALLENGER MEN - SINGLES%' then 3
                                WHEN competition_type like '%CHALLENGER WOMEN - SINGLES%' then 4
                                WHEN competition_type  '%ITF MEN - SINGLES%' then 5
                                WHEN competition_type = '%ITF WOMEN - SINGLES%' then 6 
                                       END DESC")
                  ->select('matches.*');

The thing is that if I add an "order by" before the case, it gives me an error. How should I change my query in order to have the column values in the way I mentioned?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire