Laravel Order By Specific String

Question

I have data on my PostgreSQL like this :

background

id | Size
1  | small
2  | medium
3  | large

art

id | name       | background_id
1  | Shine      | 1
2  | Sun        | 3
3  | Mountain   | 3

I want to get art data orderBy background by it's value from small (small, medium, large) with laravel eloquent

Art::with( [ 'background' => function( $background ) {
    $background->oderBy( DB::raw( "what is this?" ) );
} ] )->get();

How to fix it?

FIELD(type, 'S', 'M', 'L', 'XL')

is not working

Answer 1

You can use a CASE WHEN expression along with orderByRaw():

$queryOrder = "CASE WHEN Size = 'small' THEN 1 ";
$queryOrder .= "WHEN Size = 'medium' THEN 2 ";
$queryOrder .= "ELSE 3 END";
Art::with('background')
->orderByRaw($queryOrder);
->get();