4

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+"%")
        }
    }
Eklavya
  • 17,618
  • 4
  • 28
  • 57
Robert Jeers
  • 121
  • 1
  • 2
  • 9
  • You can fallback to SQL which is more efficient and understandable – Norbert May 27 '20 at 16:25
  • i recommend to check https://medium.com/avitotech/how-to-work-with-postgres-in-go-bad2dabd13e4 , it will be easier and probably more idiomatic – Oleg May 27 '20 at 16:50

2 Answers2

4

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
Eklavya
  • 17,618
  • 4
  • 28
  • 57
3

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.

Robert Jeers
  • 121
  • 1
  • 2
  • 9