Conditional order by


#1

Happy New Year!

Is it possible to make this order by conditional:

 .with('depothourrows', depothourrowsQuery =>
                depothourrowsQuery.orderBy('day', 'asc')
            )

What i want is to check if there is something in the date column and if so sort by date otherwise sort by day. I found this but i’m not sure how to do this:

order by
    CASE WHEN date
    date
    ELSE
    day
    END

I guess i’ll have to use a raw query?


#2

Hi, at least for me would be better to manage this situation with join method and orderBy method from the Lucid ORM in this way.

Also I use the Database helper and .raw() method to achieve the same query inside

Check this example


const data = await User.query()
                    .select('users.nameUser', 'posts.namePost')
                    .join('posts', 'users.id', '=', 'posts.user_id')
                    .orderBy(Database.raw("CASE WHEN users.id THEN users.id ELSE users.created_at END"))
                    .fetch()
        return data

Also the correct sintax to use CASE WHEN is


ORDER BY CASE WHEN users.id THEN users.id ELSE users.created_at END DESC;

Remember declare at the top of your controller the following line, to be able to use it inside your queries


const Database = use('Database')


#3

Thank you so much @ShadowPaz for answering my question.

I just thought that chaining order by makes the latter one take effect?

I need to detect if field date is not empty and if not sort by date otherwise sort my day.


#4

greetings,check if my update is helpful for you @peter


#5

This works exactly as i want:

.with('depothourrows', depothourrowsQuery =>
                    depothourrowsQuery.orderBy(
                        Database.raw('CASE WHEN date IS NULL THEN day::TEXT ELSE date END')
                    )
                )

Thank you so much @ShadowPaz