如何在 gorm 中处理多个查询

Question

我正在尝试使用 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但无法正常工作

Answer 1

您可以使用subQueryvar data []SlimeResponsedb := service.gormdbsubQueryHoldingOn := 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部分Selectvar data []SlimeResponsedb := service.gormdbdb = 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)