如何对列表的所有元素进行“和”运算符？ [关闭]

Question

i have the books with different tags (crime, fantastic, dramatic etc.). that's my sql-code:

query := `
    SELECT gotoboox.books.id, gotoboox.books.title 
    FROM gotoboox.books 
    LEFT JOIN gotoboox.books_tags ON gotoboox.books.id = gotoboox.books_tags.book_id 
    LEFT JOIN gotoboox.tags ON gotoboox.books_tags.tag_id = gotoboox.tags.id 
    WHERE gotoboox.tags.title IN ($1) 
    GROUP BY gotoboox.books.title, gotoboox.books.id
`
rows, err := p.Db.Query(query, pq.Array(tags))

but i have got empty result. for example, if i write

..WHERE gotoboox.tags.title IN ('Crime', 'Comedia').. // WITHOUT pg.Array()

its okay.

so, i need pass correctly my pq.Array(tags) to the 'where in'-statement.

P.S. tags is a slice of strings. "tags []string"

Answer 1

Something like this:

gotoboox.tags.title IN ('Crime', 'Comedia')

is, more or less, a short way to write:

gotoboox.tags.title = 'Crime' or gotoboox.tags.title = 'Comedia'

so you don't want to supply an array for the placeholder in IN ($1) unless tags.title is itself an array (which it isn't).

If you want to pass a slice for the placeholder and use pq.Array, you want to use = ANY(array) in the SQL:

query := `... WHERE gotoboox.tags.title = any ($1) ...`
rows, err := p.Db.Query(query, pq.Array(tags))

Alternatively, if tags had n elements then you could build a string like:

"$1,$2,...,$n"

fmt.Sprintf that into your SQL (which is perfectly safe since you know exactly what's in the strings):

p := placeholders(len(tags))
q := fmt.Sprintf("select ... where gotoboox.tags.title in (%s) ...", p)

and then supply values for all those placeholders when you query:

rows, err := p.DB.Query(q, tags...)