Golang Gorm:相同的查询以不同的方式构建会产生不同的结果。

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

Golang Gorm: Same query constructed differently throwing different results

问题

我想运行以下查询:

SELECT *
FROM artists
WHERE name LIKE '%roll%';

WHERE 子句将是动态的,意味着我将通过迭代一个映射来构建它,并将其链接到主查询。

话虽如此,在尝试迭代之前,我考虑测试方法链接(我对 Gorm 还不熟悉),所以我运行了以下代码:

var artist entities.Artist

query := as.db.Model(&artist)

query.Where("name LIKE ?", "%roll%")
if err := query.Find(&as.Artists).Error; err != nil {
    return err
}

你可以看到,我在链接查询的不同部分,并用 Find 结束。这将返回表中的所有元素。在打印出执行的查询后,我得到了以下结果:

SELECT * FROM `artists`  WHERE `artists`.`deleted_at` IS NULL

没有提到 LIKE 子句,而且我不知道 deleted_at IS NULL 是从哪里来的。尽管目前并不重要,因为最终我也会将其添加到查询中。

但是,如果我运行以下代码:

var artist entities.Artist

query := as.db.Model(&artist)

if err := query.Where("name LIKE ?", "%roll%").Find(&as.Artists).Error; err != nil {
    return err
}

我得到了我期望的结果。执行的查询是:

SELECT * FROM `artists`  WHERE `artists`.`deleted_at` IS NULL AND ((name LIKE '%roll%'))

有什么想法是怎么回事吗?

英文:

I want to run the following query:

SELECT *
FROM artists
WHERE name LIKE '%roll%';

the WHERE clause will be dynamic, meaning that I'll iterate through a map to build it and chain it to the main query.

Having said that, before even trying the iterate I thought about testing the method chaining (I'm new with Gorm) so I ran:

var artist entities.Artist

query := as.db.Model(&artist)

query.Where("name LIKE ?", "%roll%")
if err := query.Find(&as.Artists).Error; err != nil {
	return err
}

as you can see I'm chaining different parts of the query and finalizing it with Find. This is returning all elements in the table. After printing out the executed query I'm getting:

SELECT * FROM `artists`  WHERE `artists`.`deleted_at` IS NULL

no mention of the LIKE clause, furthermore, I don't know where the deleted_at IS NULL is coming from. Although at the moment it doesn't matter since ultimately I was gonna add that to the query as well.

But if I run:

var artist entities.Artist

query := as.db.Model(&artist)

if err := query.Where("name LIKE ?", "%roll%").Find(&as.Artists).Error; err != nil {
	return err
}

I'm getting the results I'm expecting. The executed query is:

SELECT * FROM `artists`  WHERE `artists`.`deleted_at` IS NULL AND ((name LIKE '%roll%')) 

Any idea what's going on?

答案1

得分: 1

在文档(https://gorm.io/docs/query.html)中,所有使用query.Where()的示例都展示了链式调用,就像你的第二个示例中一样。

我得出的结论是,Where()不会修改query实例的SQL,而是返回一个包含条件的新查询实例。

英文:

All the examples of using query.Where() in the documentation (https://gorm.io/docs/query.html) show chaining, like in your second example.

I conclude that Where() does not modify the SQL of the instance query, it returns a new query instance with the condition included.

答案2

得分: 1

我不知道deleted_at IS NULL是从哪里来的。

你可能在模型中使用了gorm.Model,其中包括了DeleteAt字段,这就是导致查询中添加deleted_at IS NULL的原因。DeleteAt用于检查记录是否已删除,如果已删除,则不包含在结果中。

关于LIKE子句没有提及。

var artist entities.Artist

query := as.db.Model(&artist)

query.Where("name LIKE ?", "%roll%") <- 将返回新的查询
if err := query.Find(&as.Artists).Error; err != nil {
    return err
}

正如Bill所指出的,Where返回一个带有修改条件的新查询,但由于你没有给它赋值,所以它不会被添加到查询中。

var artist entities.Artist

query := as.db.Model(&artist)

if err := query.Where("name LIKE ?", "%roll%").Find(&as.Artists).Error; err != nil {
    return err
}

在这里,你在新返回的查询上使用了Find,该查询包含了条件,因此它按预期工作。

要在查询中链接多个条件,可以通过多种方式实现:

1:

var artist entities.Artist

query := as.db.Model(&artist)

query = query.Where("name LIKE ?", "%roll%")

if err := query.Find(&as.Artists).Error; err != nil {
    return err
}

2:

var artist entities.Artist

query := as.db.Model(&artist)

err := query.
          Where("name LIKE ?", "%roll%").
          Where("name LIKE ?", "%m%").
          Find(&as.Artists).
          Error
if err != nil {
    return err
}
英文:

> I don't know where the deleted_at IS NULL is coming from

You may be extending your model with gorm.Model which include DeleteAt field which is cause of deleted_at IS NULL being added to the query. DeleteAt is used to check if the record is deleted then not include it in result

> no mention of the LIKE clause

var artist entities.Artist

query := as.db.Model(&amp;artist)

query.Where(&quot;name LIKE ?&quot;, &quot;%roll%&quot;) &lt;- will return new query
if err := query.Find(&amp;as.Artists).Error; err != nil {
    return err
}

As Bill pointed Where returns new query with modified conditions however since you are not assigning it anything it will not be added to query

var artist entities.Artist

query := as.db.Model(&amp;artist)

if err := query.Where(&quot;name LIKE ?&quot;, &quot;%roll%&quot;).Find(&amp;as.Artists).Error; err != nil {
    return err
}

Here you are using Find over the newly returned query which includes the condition hence it works as expected.

To chain multiple where condition in queries you can achieve in various ways:

1:

var artist entities.Artist

query := as.db.Model(&amp;artist)

query = query.Where(&quot;name LIKE ?&quot;, &quot;%roll%&quot;)

if err := query.Find(&amp;as.Artists).Error; err != nil {
    return err
}

2:

var artist entities.Artist

query := as.db.Model(&amp;artist)

err := query.
          Where(&quot;name LIKE ?&quot;, &quot;%roll%&quot;).
          Where(&quot;name LIKE ?&quot;, &quot;%m%&quot;).
          Find(&amp;as.Artists).
          Error
if err != nil {
    return err
}

huangapple
  • 本文由 发表于 2022年6月5日 23:19:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/72508592.html
匿名

发表评论

匿名网友

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

确定