如何在 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 []SlimeResponsedb := service.gormdbdb = 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

您可以使用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部分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)