GORM golang SQL query performing case insensitive search not working

Question content

I want to do a case-insensitive search in Golang. I'm using this library.

I have tried the following but it doesn't work.

someId = "abc"
model := abcModel{Id: someId}
result := p.db.Where("lower(id) = lower(?)", someId).Take(&model)

<code>
Id is primary-key here
</code>

I also tried it

db.Where("LOWER(id) LIKE LOWER(?)", fmt.Sprintf("%%%s%%", someId)).Take(&model)

Can anyone help? Not sure what's wrong. Any pointers would be greatly appreciated.

Thanks!

edit:

This is what I have in the database

id      |          created_at           |          updated_at           | deleted_at |  client_id | ttl  |             client_type              |    token  |          expires_on          
--------------------------------------+-------------------------------+-------------------------------+------------+--------------------------------------+------+--------------------------------------+
        ABC      | 2023-10-30 16:10:59.614132+00 | 2023-10-30 16:10:59.614132+00 |            |  ae30e377  | 100  | 7da0e618-7393-45c2-94dc-5e7b1d6c1610 |   abc     | 2023-10-30 16:27:39.613566+00

I would expect the above query to return this record in the database since it is a case insensitive search.

The error I received was record not found https://gorm.io/docs/error_handling.html#ErrRecordNotFound

I'm running a Postgres server in a Docker container. https://hub.docker.com/_/postgres

Correct Answer

It’s not clear what p stands for, but here’s one Working example using Take() and Where().Take():

func main() {
    // open connection to postgres
    db, err := gorm.Open(postgres.New(...), &gorm.Config{})
    if err != nil {
        panic("failed to connect database")
    }

    // Get the row
    someId := "abc"
    var result abcModel
    db.Take(&result, "lower(id) = lower(?)", someId)
    // Print the row
    fmt.Println(result)

    // Find the row using Where
    var result2 abcModel
    db.Where("lower(id) = lower(?)", someId).Take(&result2)
    // Print the row
    fmt.Println(result2)

}

Output:

$ go run .
{ABC 2023-10-30 10:00:57.774905 -0700 PDT 2023-10-30 10:00:57.774905 -0700 PDT <nil> ae30e377 100 7da0e618-7393-45c2-94dc-5e7b1d6c1610 abc 2023-10-30 10:00:57.774906 -0700 PDT}
{ABC 2023-10-30 10:00:57.774905 -0700 PDT 2023-10-30 10:00:57.774905 -0700 PDT <nil> ae30e377 100 7da0e618-7393-45c2-94dc-5e7b1d6c1610 abc 2023-10-30 10:00:57.774906 -0700 PDT}

So your original code seems to work, it's just that your error may be related to how p is defined

The above is the detailed content of GORM golang SQL query performing case insensitive search not working. For more information, please follow other related articles on the PHP Chinese website!