如何按结果查找行？

Question

我有张桌子：

               room_id                |    user_id    |  role
--------------------------------------+---------------+--------
 a1d11dcb-b399-4282-9ec2-7dc3df6d1f88 | usrZK8kZTzEHC | member
 a1d11dcb-b399-4282-9ec2-7dc3df6d1f88 | usrItug3Lj2c5 | member
 c2503ab5-1ee4-428a-be1a-d561f6fab068 | usrItug3Lj2c5 | member
 c2503ab5-1ee4-428a-be1a-d561f6fab068 | usrh8hqEpSWLG | member
 9c866cc2-91f1-4658-9843-2c31cff2edfa | usrItug3Lj2c5 | member
 9c866cc2-91f1-4658-9843-2c31cff2edfa | usrh8hqEpSWLG | member
 0e63d78f-3b51-42f7-9ca3-80b93ede6a82 | usrZK8kZTzEHC | member
 0e63d78f-3b51-42f7-9ca3-80b93ede6a82 | usrh8hqEpSWLG | member

我希望用user_id=usrItug3Lj2c5选择所有行

同时，还需要查找user_id=usrItug3Lj2c5，如果room_id与该行相同，是否也会被搜索出来。

例如：

在上面，我需要搜索：

               room_id                |    user_id    |  role
--------------------------------------+---------------+--------
 a1d11dcb-b399-4282-9ec2-7dc3df6d1f88 | usrZK8kZTzEHC | member
 a1d11dcb-b399-4282-9ec2-7dc3df6d1f88 | usrItug3Lj2c5 | member
 c2503ab5-1ee4-428a-be1a-d561f6fab068 | usrItug3Lj2c5 | member
 c2503ab5-1ee4-428a-be1a-d561f6fab068 | usrh8hqEpSWLG | member
 9c866cc2-91f1-4658-9843-2c31cff2edfa | usrItug3Lj2c5 | member
 9c866cc2-91f1-4658-9843-2c31cff2edfa | usrh8hqEpSWLG | member

在这种情况下，如何用gorm写？

Answer 1

当您准备好原始sql时，这很容易。然后你可以很容易地用戈姆的方式重写它。

原始SQL

SELECT t2.* FROM "tablename" AS t2 where t2.room_id IN (SELECT t1.room_id FROM "tablename" AS t1 where t1.user_id = 'usrItug3Lj2c5')

戈姆

var res UserRoom // just assuming the type
subquery := db.Select("t1.room_id").
    Table("tablename as t1").
    Where("t1.user_id = ?", userID)

err := db.Select("t2.*").
      Table("tablename as t2").
      Where("t2.room_id IN (?)", subquery).
      Scan(&res).Error

希望这能成功！