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

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

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

问题

这是模型的样子:

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

这是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:

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

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需要一个明确的值列表(或子查询)。

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

contributors := []int{20, 25, 27}

var tmp []string

for _, v := range contributors {
    tmp = append(tmp, fmt.Sprint(v))
}

query := "SELECT * from table_name where contributors in (" + strings.Join(tmp, ",") + ")"

或者

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

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

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 :

contributors := []int{20, 25, 27}

var tmp []string

for _, v := range contributors {
    tmp = append(tmp, fmt.Sprint(v))
}

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.

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:

确定