How to Query a Database with an IN Clause Using a Slice in Go?

Querying Database Using IN Clause with a Slice in Go

In database querying, it's common to filter results based on a subset of values using the IN clause. When working with a slice of integers in Go, however, it can be challenging to create IN clauses that are both syntactically valid and idiomatic.

Consider the following query, which attempts to retrieve distinct titles from two joined tables. The IN clause is populated with the contents of a slice named artIds:

<code class="go">inq := "6,7" //strings.Join(artIds, ",")
rows, err = db.Query("SELECT DISTINCT title FROM tags_for_articles LEFT JOIN tags ON tags.id = tags_for_articles.tag_id WHERE article_id IN (?)", inq)</code>

The error handling has been omitted for brevity. The query fails because database/sql passes arguments directly to the driver, which expects a variable number of bindvars depending on the length of the slice.

Alternatively, a query that uses a static IN clause will execute successfully:

<code class="go">rows, err = db.Query("SELECT DISTINCT title FROM tags_for_articles LEFT JOIN tags ON tags.id = tags_for_articles.tag_id WHERE article_id IN (6,7)", inq)</code>

One solution to this issue is to use the sqlx library, which provides more control over database queries. The sqlx.In function can be used to process queries with IN clauses and create the necessary bindvars dynamically:

<code class="go">var levels = []int{4, 6, 7}
query, args, err := sqlx.In("SELECT * FROM users WHERE level IN (?);", levels)</code>

The resulting query and args can then be used to execute the prepared statement. Using the sqlx.In function ensures that the query is syntactically valid and can be efficiently executed with the given slice of values.

The above is the detailed content of How to Query a Database with an IN Clause Using a Slice in Go?. For more information, please follow other related articles on the PHP Chinese website!