如何在gorm中处理多个查询

huangapple
go评论110阅读模式
英文:

How to handle multiple queries in gorm

问题

我正在尝试使用gorm处理嵌套查询，但是我在解决这个问题时遇到了困难。

查询语句：

  1. SELECT smId AS 'slimeId', slStatus, slPPIV, slHighRiskSituation, 
  2. (SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'Holding on'
  3. AND rnSmId = smId) AS 'holdingOn',
  4. (SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = smId)
  5.  AS 'inSystem', smSuspectedCorona,
  6. IF(smStatusClosed != 0, 1, 0) as statusClosed, smSurge, 
  7. (SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = smId) 
  8. as 'ruinsOnHold',
  9. (SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = smId)
  10. as 'ruinsInSystem' FROM slimes WHERE slId != 0  GROUP BY slId HAVING slId > 0

我已经完成了其中一部分，这部分相当基础，问题出在嵌套查询上，我不知道该怎么处理。

  1. var data []SlimeResponse
  2. db := service.gormdb
  3. db = db.Select("smId as slimeId", "slStatus", "slPPIV", "slHighRiskSituation")
  4. db = db.Where("smId != ?", 0).Group("smId").Having("smId > ?", 0)
  5. 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:

  1. SELECT smId AS 'slimeId', slStatus, slPPIV, slHighRiskSituation, 
  2. (SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'Holding on'
  3. AND rnSmId = smId) AS 'holdingOn',
  4. (SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = smId)
  5.  AS 'inSystem', smSuspectedCorona,
  6. IF(smStatusClosed != 0, 1, 0) as statusClosed, smSurge, 
  7. (SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = smId) 
  8. as 'ruinsOnHold',
  9. (SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = smId)
  10. 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

  1. var data []SlimeResponse
  2. db := service.gormdb
  3. db = db.Select("smId as slimeId", "slStatus", "slPPIV", "slHighRiskSituation")
  4. db = db.Where("smId != ?", 0).Group("smId ").Having("smId > ?", 0)
  5. 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。

  1. var data []SlimeResponse
  2. db := service.gormdb
  3. subQueryHoldingOn := db.
  4.   Select("count(rnIg)").
  5.   Where("rnSquidStatus = 'In system' AND rnSmId = ?", smId).
  6.   Table("ruins")
  7. subQueryInSystem := db.
  8.   Select("count(rnIg)").
  9.   Where("rnSquidStatus = 'Holding on' AND rnSmId = ?", smId).
  10.   Table("ruins")
  11. groupSubQueryHoldingOn := db.
  12.   Select("GROUP_CONCAT(rnIg)").
  13.   Where("rnSquidStatus = 'In system' AND rnSmId = ?", smId).
  14.   Table("ruins")
  15. groupSubQueryInSystem := db.
  16.   Select("GROUP_CONCAT(rnIg)").
  17.   Where("rnSquidStatus = 'Holding on' AND rnSmId = ?", smId).
  18.   Table("ruins")
  19. 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)
  20. db = db.Where("smId != ?", 0).Group("smId ").Having("smId > ?", 0)
  21. db = db.Table("slimes").Find(&data)

或者

你可以直接在Select中传递选择部分。

  1. var data []SlimeResponse
  2. db := service.gormdb
  3. db = db.Select(
  4.   `
  5.     smId AS 'slimeId',
  6.     slStatus,
  7.     slPPIV,
  8.     slHighRiskSituation, 
  9.     (
  10.       SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = ?
  11.     ) AS 'holdingOn',
  12.     (
  13.       SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = ?
  14.     ) AS 'inSystem',
  15.     smSuspectedCorona,
  16.     IF(smStatusClosed != 0, 1, 0) as statusClosed,
  17.     smSurge, 
  18.     (
  19.       SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = ?
  20.     ) as 'ruinsOnHold',
  21.     (
  22.       SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = ?
  23.     ) as 'ruinsInSystem'
  24.   `, smId, smId, smId, smId
  25. )
  26. db = db.Where("smId != ?", 0).Group("smId ").Having("smId > ?", 0)
  27. db = db.Table("slimes").Find(&data)
英文:

You can use subQuery

  1. var data []SlimeResponse
  2. db := service.gormdb
  3. subQueryHoldingOn := db.
  4.   Select("count(rnIg)").
  5.   Where("rnSquidStatus = 'In system' AND rnSmId = ?", smId).
  6.   Table("ruins")
  7. subQueryInSystem := db.
  8.   Select("count(rnIg)").
  9.   Where("rnSquidStatus = 'Holding on' AND rnSmId = ?", smId).
  10.   Table("ruins")
  11. groupSubQueryHoldingOn := db.
  12.   Select("GROUP_CONCAT(rnIg)").
  13.   Where("rnSquidStatus = 'In system' AND rnSmId = ?", smId).
  14.   Table("ruins")
  15. groupSubQueryInSystem := db.
  16.   Select("GROUP_CONCAT(rnIg)").
  17.   Where("rnSquidStatus = 'Holding on' AND rnSmId = ?", smId).
  18.   Table("ruins")
  19. 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)
  20. db = db.Where("smId != ?", 0).Group("smId ").Having("smId > ?", 0)
  21. db = db.Table("slimes").Find(&data)

Or

You can directly pass the select part in Select

  1. var data []SlimeResponse
  2. db := service.gormdb
  3. db = db.Select(
  4.   `
  5.     smId AS 'slimeId',
  6.     slStatus,
  7.     slPPIV,
  8.     slHighRiskSituation, 
  9.     (
  10.       SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = ?
  11.     ) AS 'holdingOn',
  12.     (
  13.       SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = ?
  14.     ) AS 'inSystem',
  15.     smSuspectedCorona,
  16.     IF(smStatusClosed != 0, 1, 0) as statusClosed,
  17.     smSurge, 
  18.     (
  19.       SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = ?
  20.     ) as 'ruinsOnHold',
  21.     (
  22.       SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = ?
  23.     ) as 'ruinsInSystem'
  24.   `, smId, smId, smId, smId
  25. )
  26. db = db.Where("smId != ?", 0).Group("smId ").Having("smId > ?", 0)
  27. db = db.Table("slimes").Find(&data)

huangapple
  • 本文由 发表于 2022年5月18日 02:03:39
  • 转载请务必保留本文链接：https://go.coder-hub.com/72278723.html
  • go
  • go-gorm
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定