Gorm How to create a query CASE WHEN

I want to create a query like this CASE WHEN but i do not see any option or anything like that in docs

SELECT id, 
    CASE WHEN title ILIKE '%bag%' THEN 0 ELSE 1 END AS title_match,
   title
FROM items as i WHERE title ILIKE '%bag%'
ORDER BY title_match

how do i achieve the case when query

Expected answer

I want to add something like this to my query CASE WHEN title ILIKE '%bag%' THEN 0 ELSE 1 END AS title_match

Comment From: jinzhu

Select accepts SQL expression

Comment From: Reidsy

Commenting on this because it is high up on google when searching for CASE WHEN with Gorm.

This is by no means production quality code, but may give others a starting point who want an alternative other than raw sql.

import (
    "fmt"

    "gorm.io/gorm/clause"
)

type OrderByCase struct {
    Column clause.Column
    Values map[string]int
    Desc   bool
}

// Name where clause name
func (orderBy OrderByCase) Name() string {
    return "ORDER BY"
}

// Build build where clause
func (orderByCase OrderByCase) Build(builder clause.Builder) {
    builder.WriteString("CASE ")
    builder.WriteQuoted(orderByCase.Column)
    for field, weight := range orderByCase.Values {
        builder.WriteString(fmt.Sprintf(" WHEN '%s' THEN %d", field, weight)) // potential sql injection risk
    }
    builder.WriteString(" END")
    if orderByCase.Desc {
        builder.WriteString(" DESC")
    }
}

// MergeClause merge order by clauses
func (orderByCase OrderByCase) MergeClause(clause *clause.Clause) {
    clause.Expression = orderByCase
}

Usage is like the following:

u := User{}
db.ToSQL(func(tx *gorm.DB) *gorm.DB {
    return tx.Clauses(
        OrderByCase{
        Column: clause.Column{Name: "title"},
        Values: map[string]int{
            "STAFF": 10,
            "CUSTOMER": 20,
        },
        Desc: true,
    },
).Limit(1).Find(&u)

To produce the SQL

SELECT * FROM "users"
ORDER BY CASE "title"
WHEN 'STAFF' THEN 10
WHEN 'CUSTOMER' THEN 20
END DESC
LIMIT 1

Comment From: dgunay

Fair warning that GORM tends to stomp on @Reidsy 's solution. Example: if you use .First then GORM overwrites the existing ORDER BY clause with its own, since it is only able to merge with other clause.OrderBys: https://github.com/go-gorm/gorm/blob/8fb9a317756bc07dcaaa82d43613ddcf9295c1ad/clause/order_by.go#L39-L53

I was able to get multiple ORDER BY clauses to play nice together by doing this:

tx := db.
                 Where("etc = ?", 1).
        Clauses(
            clause.OrderBy{
                Expression: clause.CommaExpression{
                    Exprs: []clause.Expression{
                        OrderByCase{ /* etc */ },
                        clause.Expr{SQL: "created_at DESC"},
                    },
                },
            },
        ).
        Find(&entity)

Comment From: rblaszcz

I would like to expand @jinzhu's answer because other answers focus into putting CASE inside ORDER BY clause instead of SELECT which is not what the OP was asking for.

The simplest and gorm compatible way to achieve this is to use CASE inside SELECT statement using gorm's Select method which can take SQL code. By creating a new column using CASE inside SELECT and naming this column you can then sort by this new column's name in ORDER BY clause.

example code:

res := db.
        Select("CASE status WHEN 'BLOCKED' THEN 1 WHEN 'COMPLETED' THEN 10 ELSE 5 END AS status_order").
        Order("status_order").
        Find(&entities)