如何创建一个查询,检查数组是否包含某个值?使用 golang gorm。

huangapple go评论141阅读模式
英文:

How to create query that checks if array contains value? golang gorm

问题

这是模型的样子:

  1. type Board struct {
  2. Id uint `gorm:"primaryKey;autoIncrement;unique" json:"id"`
  3. Owner uint `json:"owner"`
  4. Name string `json:"name"`
  5. Contributors datatypes.JSON `gorm:"type:jsonb" json:"contributors"`
  6. GeneratedLink string `gorm:"default:''" json:"generated_link"`
  7. Todos datatypes.JSON `gorm:"type:jsonb" json:"todos"`
  8. }

这是postgresql列中contributors值的样子:

如何创建一个查询,检查数组是否包含某个值?使用 golang gorm。

如何查询包含20的contributors数组?我尝试像这样做:database.DB.Where("contributors IN ?", 20).Find(&contBoards),但是出现错误:ERROR: syntax error at or near "$1" (SQLSTATE 42601)

请问有什么想法或选项吗?
附注:使用gorm和postgresql。

英文:

This is how model looks:

  1. type Board struct {
  2. Id uint `gorm:"primaryKey;autoIncrement;unique" json:"id"`
  3. Owner uint `json:"owner"`
  4. Name string `json:"name"`
  5. Contributors datatypes.JSON `gorm:"type:jsonb" json:"contributors"`
  6. GeneratedLink string `gorm:"default:''" json:"generated_link"`
  7. Todos datatypes.JSON `gorm:"type:jsonb" json:"todos"`
  8. }

This is how contributors value looks in the postgresql column:

如何创建一个查询,检查数组是否包含某个值?使用 golang gorm。

and how to make query that checks that contributors array contains for example 20?
i tried to do like this: database.DB.Where("contributors IN ?", 20).Find(&contBoards)
but got error: ERROR: syntax error at or near "$1" (SQLSTATE 42601)

Please any ideas, any options.
P.S using gorm, postgresql

答案1

得分: 3

你在WHERE子句中使用IN运算符来检查一个值是否与值列表中的任何值匹配。

IN需要一个明确的值列表(或子查询)。

我为你的情况创建了一个示例场景,如下所示:

  1. contributors := []int{20, 25, 27}
  2. var tmp []string
  3. for _, v := range contributors {
  4. tmp = append(tmp, fmt.Sprint(v))
  5. }
  6. query := "SELECT * from table_name where contributors in (" + strings.Join(tmp, ",") + ")"

或者

ANY与数组一起使用。如果你已经有了值列表的数组,这将非常有用。

使用ANY运算符,你可以搜索一个值。

  1. select * from table_name where value = ANY(contributors);

如果你想搜索多个值,可以使用@>运算符。

@>是“包含”运算符。

定义如下的几种数据类型:

数组:http://www.postgresql.org/docs/current/static/functions-array.html

范围类型:http://www.postgresql.org/docs/current/static/functions-range.html

几何类型:http://www.postgresql.org/docs/current/static/functions-geometry.html

JSON(和JSONB):http://www.postgresql.org/docs/current/static/functions-json.html

为了更好地理解,你可以参考这个链接:https://stackoverflow.com/questions/39643454/postgres-check-if-array-field-contains-value

英文:

You use IN operator in the WHERE clause to check if a value matches any value in a list of values.

IN expects an explicit list of values (or a subquery).

I have created a sample scenario for your case as follows :

  1. contributors := []int{20, 25, 27}
  2. var tmp []string
  3. for _, v := range contributors {
  4. tmp = append(tmp, fmt.Sprint(v))
  5. }
  6. query := "SELECT * from table_name where contributors in (" + strings.Join(tmp, ",") + ")"

OR

ANY works with arrays. This can be useful if you have the list of values already in an array.

With ANY operator you can search for only one value.

  1. select * from table_name where value = ANY(contributors);

If you want to search multiple values, you can use @> operator.

@> is the "contains" operator.

Defined for several data types as follows :

arrays: http://www.postgresql.org/docs/current/static/functions-array.html

range types: http://www.postgresql.org/docs/current/static/functions-range.html

geometric types: http://www.postgresql.org/docs/current/static/functions-geometry.html

JSON (and JSONB): http://www.postgresql.org/docs/current/static/functions-json.html

For better understanding you can refer this link : https://stackoverflow.com/questions/39643454/postgres-check-if-array-field-contains-value

huangapple
  • 本文由 发表于 2021年12月13日 01:34:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/70326030.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定