go - gorm 查询 OR
问题描述
我一直在生成一个在运行时动态创建的查询。
我想在中间创建一个having
查询,例如OR
SELECT name FROM `user_group` WHERE ((group_key = 'age' AND group_value = '20'))
OR ((group_key = 'division' AND group_value = 'accounting'))
OR ((group_key = 'age' AND group_value = '22'))
OR ((group_key = 'division' AND group_value = 'kitchen'))
GROUP_BY name
HAVING
((SUM(group_key = 'age' AND group_value = '20') > 0)
AND
(SUM(group_key = 'division' AND group_value = 'accounting') > 0))
OR
((SUM(group_key = 'age' AND group_value = '22') > 0)
AND
(SUM(group_key = 'division' AND group_value = 'kitchen') > 0))
请注意,OR
内部having
声明是我要问的。
我目前用gorm得到了这个:
SELECT name FROM `user_group` WHERE ((group_key = 'age' AND group_value = '20'))
OR ((group_key = 'division' AND group_value = 'accounting'))
OR ((group_key = 'age' AND group_value = '22'))
OR ((group_key = 'division' AND group_value = 'kitchen'))
GROUP_BY name
HAVING
((SUM(group_key = 'age' AND group_value = '20') > 0)
AND
(SUM(group_key = 'division' AND group_value = 'accounting') > 0))
AND
((SUM(group_key = 'age' AND group_value = '22') > 0)
AND
(SUM(group_key = 'division' AND group_value = 'kitchen') > 0))
注意声明AND
中的having
这是查询生成:
for _, condition := range resp.Allow.Conditions {
for key, val := range condition {
if len(key) <= 0 || len(val) <= 0 {
continue
}
groupQuery = groupQuery.Or("(group_key = ? AND group_value = ?)", key, val)
groupQuery = groupQuery.Having("SUM(group_key = ? AND group_value = ?) > 0", key, val)
}
}
groupQuery = groupQuery.Group('name')
有什么方法可以做到这一点gorm
吗?我查看了文档,我最好的选择是它必须是一个原始的 sql 查询。我不喜欢它,但如果这是唯一的方法,那也没关系。
注意:我使用 mysql 作为方言
解决方案
该行的输出:
groupQuery = groupQuery.Having("SUM(group_key = ? AND group_value = ?) > 0", key, val)
是块
((SUM(group_key = 'age' AND group_value = '20') > 0)
AND
(SUM(group_key = 'division' AND group_value = 'accounting') > 0))
AND
((SUM(group_key = 'age' AND group_value = '22') > 0)
AND
(SUM(group_key = 'division' AND group_value = 'kitchen') > 0))
哪个是对的。查看每行的所有左括号和右括号:
````(COND1) AND (COND2) AND (COND3) AND (COND4)```
根据您的要求获得一个单一的或在有声明的中间:
((SUM(group_key = 'age' AND group_value = '20') > 0)
AND
(SUM(group_key = 'division' AND group_value = 'accounting') > 0))
AND
((SUM(group_key = 'age' AND group_value = '22') > 0)
AND
(SUM(group_key = 'division' AND group_value = 'kitchen') > 0))
这将是:
(COND1) AND (COND2) OR (COND3) AND COND(4)
会导致不太预期的结果。
更合乎逻辑的是:
(COND1) OR (COND2) OR (COND3) OR COND(4)
或者:
((COND1) AND (COND2)) OR ((COND3) AND COND(4))
最后一个版本(这似乎是您的目标),不能如所述在循环中生成,并且需要特定的方法。
看起来你几乎只能使用原始 SQL。
推荐阅读
- python - 如何从存储在 gitlab repos 中的文件中提取内容
- javascript - 将图像动态添加到这个漂亮的框架中
- javascript - Email expiration tokens without usage of database and crontab deletion
- python - Kivy 相机免费资源问题
- javascript - why is the onclick event not working in javascript when the button and function reference both show the right objects?
- java - 为什么我在尝试在 Spring Boot 应用程序上配置数据库连接时获得此异常?创建名为“dataSource”的 bean 时出错
- android - Find and Replace in entire project in Android Studio
- php - Laravel:从今天开始查询所有带有时间戳的记录
- php - php中的CDN链接包含文件
- github - im having trouble with git push ive t