Case

case is an option in select query which is used to change stored value based on some condition. It is similar to multiple if else statetement. So once a condition is true it is stopped and value is returned.

const results = await connection.select({
    from: 'Customers',
    case: {
        city: [{
            '=': 'London',
            then: 'London UK'
        }, {
            then: 'World'
        }]
    }
})

You can use other operators symbol similar to '=' used above - '>', '>=, '<' ,'<=', '!='

Example

Note : -

• If you want to return stored value instead of custom value - provide null value in then - { then:null }
• This is not used to filter values but to change value. To filter value where is used.

Order By

case can be also used in order query to change the ordering of result.

Change ordering

Consider - in Customers table : record contains values - "Argentina", "Austria" , etc. in column "country". When sorting by coluntry in ascending order - the record "Argentina" comes first & then "Austria". But for some reason we want Austria to comes first.

In this case we will have to use case query in order & provide a value for "Austria" which is lesser than "Argentina".

const results = await connection.select({
    from: 'Customers',
    order: {
        by: 'country',
        case: [{
            '=': 'Austria',
            then: "a" // telling value of 'Austria is a'
        }, {
            then: null
        }]
    }
})

Note :- Use same data type as column in then value otherwise you might get some error. In the above example - i have provided string value "a", as country data type is string.

Example

Change column

Another scenario is when you want to change order by column based on some condition.

select({
    from: 'Customers',
    order: {
        by: {
            'country': [{
                '=': 'Spain',
                then: "city"
            }, {
                then: 'country'
            }]
        }
    }
})

Example

Group By

case can be used in group by for grouping values dynamically.

e.g -

const results = await connection.select({
    from: 'Products',
    groupBy: {
        'price': [{
            '<=': 100,
            then: 'affordable item'
        }, {
            then: 'costly item'
        }]
    }
})

Example