Gorm how to find rows by result?

user_6744309 0
Viewed 113

I have a table:

               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

I want select all rows out with user_id=usrItug3Lj2c5

meanwhile, also need found out if user_id=usrItug3Lj2c5, if room_id same as that row, will also be searched out.

For example:

in above, I need search out:

               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

How to write in gorm in this situation?

go-gorm go grails-orm
1 Answers

It is easy when you have the raw sql ready. Then you can easily rewrite that in gorm way.

Raw 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')

Gorm

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

Hopefully this will work!

user_66075620
Related