Querying GORM database by its pq.StringArray attribute

Question

I have the following gorm.Model and I want to Query my Postgres database to return Confessions that have a specific category in their .Categories attribute, but I have no idea how to Query inside a pq.StringArray. Is there a work-around?

type Confession struct {
    gorm.Model
    User       string         `json:"User"`
    Title      string         `json:"Title"`
    Body       string         `json:"Body"`
    Mood       string         `json:"Mood"`
    Categories pq.StringArray `gorm:"type:varchar(64)[]" json:"Categories"`
}

And here is how I tried to query, but using the LIKE operator throws an error.

if categories != nil {
        for _, cat := range categories {
            tx = tx.Where("Categories LIKE ?", "%"+cat+"%")
        }
    }

Answer 1

Use <@ for array contains. You can query using Query function of *sql.DB and get *sql.DB using tx.DB() in gorm

sel := "SELECT * FROM confessions WHERE $1 <@ categories"
categories := []string{"cat1", "cat2"}
rows, err := tx.DB().Query(sel, pq.Array(categories))

Or try Gorm Raw SQL , but I won't sure it will work properly or not for array functions.

References:

• PostgreSQL Array function here
• ProtgreSQL Array use in golang here

Answer 2

The easiest solution to my problem was to use the .Where command as such

tx = tx.Where("categories && ?", pq.Array(categories))

This will return a gorm.DB so I can continue to chain actions. The && operator is to check for OVERLAPPING elements.