英文:
How to handle multiple queries in gorm
问题
我正在尝试使用gorm处理嵌套查询，但是我在解决这个问题时遇到了困难。
查询语句：
SELECT smId AS 'slimeId', slStatus, slPPIV, slHighRiskSituation,
(SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'Holding on'
AND rnSmId = smId) AS 'holdingOn',
(SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = smId)
AS 'inSystem', smSuspectedCorona,
IF(smStatusClosed != 0, 1, 0) as statusClosed, smSurge,
(SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = smId)
as 'ruinsOnHold',
(SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = smId)
as 'ruinsInSystem' FROM slimes WHERE slId != 0 GROUP BY slId HAVING slId > 0
我已经完成了其中一部分，这部分相当基础，问题出在嵌套查询上，我不知道该怎么处理。
var data []SlimeResponse
db := service.gormdb
db = db.Select("smId as slimeId", "slStatus", "slPPIV", "slHighRiskSituation")
db = db.Where("smId != ?", 0).Group("smId").Having("smId > ?", 0)
db = db.Table("slimes").Find(&data)
我尝试使用Where方法，但是和使用Joins方法一样都没有成功。我无法让它们正常工作。
英文:
I am trying to handle nested queries using gorm and I am having troudble figuring this out.
The queries:
SELECT smId AS 'slimeId', slStatus, slPPIV, slHighRiskSituation,
(SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'Holding on'
AND rnSmId = smId) AS 'holdingOn',
(SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = smId)
AS 'inSystem', smSuspectedCorona,
IF(smStatusClosed != 0, 1, 0) as statusClosed, smSurge,
(SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = smId)
as 'ruinsOnHold',
(SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = smId)
as 'ruinsInSystem' FROM slimes WHERE slId != 0 GROUP BY slId HAVING slId > 0
So I can get some of it done which is pretty basics, the problem is when nesting comes which I don't know what to do in that case
var data []SlimeResponse
db := service.gormdb
db = db.Select("smId as slimeId", "slStatus", "slPPIV", "slHighRiskSituation")
db = db.Where("smId != ?", 0).Group("smId ").Having("smId > ?", 0)
db = db.Table("slimes").Find(&data)
Tried using Where method but didn't work same as Joins but couldn't get it to work
答案1
得分: 1
你可以使用subQuery。
var data []SlimeResponse
db := service.gormdb
subQueryHoldingOn := db.
Select("count(rnIg)").
Where("rnSquidStatus = 'In system' AND rnSmId = ?", smId).
Table("ruins")
subQueryInSystem := db.
Select("count(rnIg)").
Where("rnSquidStatus = 'Holding on' AND rnSmId = ?", smId).
Table("ruins")
groupSubQueryHoldingOn := db.
Select("GROUP_CONCAT(rnIg)").
Where("rnSquidStatus = 'In system' AND rnSmId = ?", smId).
Table("ruins")
groupSubQueryInSystem := db.
Select("GROUP_CONCAT(rnIg)").
Where("rnSquidStatus = 'Holding on' AND rnSmId = ?", smId).
Table("ruins")
db = db.Select("smId as slimeId, slStatus, slPPIV, slHighRiskSituation, (?) as holdingOn, (?) as inSystem, smSuspectedCorona, IF(smStatusClosed != 0, 1, 0) as statusClosed, smSurge, (?) as ruinsOnHold, (?) as ruinsInSystem", subQueryHoldingOn, subQueryInSystem, groupSubQueryHoldingOn, groupSubQueryInSystem)
db = db.Where("smId != ?", 0).Group("smId ").Having("smId > ?", 0)
db = db.Table("slimes").Find(&data)
或者
你可以直接在Select中传递选择部分。
var data []SlimeResponse
db := service.gormdb
db = db.Select(
`
smId AS 'slimeId',
slStatus,
slPPIV,
slHighRiskSituation,
(
SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = ?
) AS 'holdingOn',
(
SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = ?
) AS 'inSystem',
smSuspectedCorona,
IF(smStatusClosed != 0, 1, 0) as statusClosed,
smSurge,
(
SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = ?
) as 'ruinsOnHold',
(
SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = ?
) as 'ruinsInSystem'
`, smId, smId, smId, smId
)
db = db.Where("smId != ?", 0).Group("smId ").Having("smId > ?", 0)
db = db.Table("slimes").Find(&data)
英文:
You can use subQuery
var data []SlimeResponse
db := service.gormdb
subQueryHoldingOn := db.
Select("count(rnIg)").
Where("rnSquidStatus = 'In system' AND rnSmId = ?", smId).
Table("ruins")
subQueryInSystem := db.
Select("count(rnIg)").
Where("rnSquidStatus = 'Holding on' AND rnSmId = ?", smId).
Table("ruins")
groupSubQueryHoldingOn := db.
Select("GROUP_CONCAT(rnIg)").
Where("rnSquidStatus = 'In system' AND rnSmId = ?", smId).
Table("ruins")
groupSubQueryInSystem := db.
Select("GROUP_CONCAT(rnIg)").
Where("rnSquidStatus = 'Holding on' AND rnSmId = ?", smId).
Table("ruins")
db = db.Select("smId as slimeId, slStatus, slPPIV, slHighRiskSituation, (?) as holdingOn, (?) as inSystem, smSuspectedCorona, IF(smStatusClosed != 0, 1, 0) as statusClosed, smSurge, (?) as ruinsOnHold, (?) as ruinsInSystem", subQueryHoldingOn, subQueryInSystem, groupSubQueryHoldingOn, groupSubQueryInSystem)
db = db.Where("smId != ?", 0).Group("smId ").Having("smId > ?", 0)
db = db.Table("slimes").Find(&data)
Or
You can directly pass the select part in Select
var data []SlimeResponse
db := service.gormdb
db = db.Select(
`
smId AS 'slimeId',
slStatus,
slPPIV,
slHighRiskSituation,
(
SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = ?
) AS 'holdingOn',
(
SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = ?
) AS 'inSystem',
smSuspectedCorona,
IF(smStatusClosed != 0, 1, 0) as statusClosed,
smSurge,
(
SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = ?
) as 'ruinsOnHold',
(
SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = ?
) as 'ruinsInSystem'
`, smId, smId, smId, smId
)
db = db.Where("smId != ?", 0).Group("smId ").Having("smId > ?", 0)
db = db.Table("slimes").Find(&data)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论