英文:
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(&artist)
query.Where("name LIKE ?", "%roll%") <- will return new query
if err := query.Find(&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(&artist)
if err := query.Where("name LIKE ?", "%roll%").Find(&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(&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
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论