Gorm How to query a many2many relationship with a Where clause on the association?

Your Question

I have the following entities:

type User struct {
  ID     string
  Name   string
  Groups []Group `gorm:"many2many:users_groups"`
}

type Group struct {
  ID   string
  Name string
}

I know I can preload the groups using

var users []Users
db.Preload("Groups").Find(&users)

And I can also filter Users using

var users []Users
db.Preload("Groups").Where("name IN ?", []string{"name1", "name2"}).Find(&users)

This will bring all the User that have name equals "name1" or "name2"

But I cannot filter Users based on Groups

var users []Users
db.Preload("Groups", "name IN ?", []string{"groupname"}).Find(&users)

I expect it to bring all Users that have Group Name equal "groupname"

How can I achieve this using database only? (my database is big and I cannot load all users into memory and filter it in the application)

Comment From: jinzhu

I expect it to bring all Users that have Group Name equal "groupname"

You need to use join in this case

Comment From: lopes-gustavo

But how can I do that?

If I use .Join, I'll end up with repeated values... And the only way I could imagine using Join is mapping the rows myself into the entities. But if I have to map myself the rows into the models, there's no point in using an ORM.

Is there a way to do that automatically that I'm not seeing?

Someone suggested in this StackOverflow question to use Join inside the Preload, but that simply filter the association, instead of the main model.

Comment From: take

@lopes-gustavo were you able to find the solution for this problem? 👀 I'm having the same problem

Comment From: lopes-gustavo

No, sorry. I ended up using Join and mapping the rows myself. Not the best solution, but it worked in the timeframe we had

Comment From: AlexThurston

Is using Join the only way to solve this? I'm implementing a basic tagging structure and am surprised that this isn't simpler.

Comment From: genofire

With using Join i got duplicated entries of User if the User has both Groups in the Join(, WHERE)

Comment From: or-shek

you can use: db.Preload("Groups", "name IN (?)", []string{"name1", "name2"}).Find(&users)

Comment From: genofire

@or-shek it also just filter the Groups, not the users (by there groups)

Comment From: goxiaoy

db.Joins("JOIN user_groups on user_groups.user_id = users.id JOIN groups on user_groups.group_id = groups.id AND groups.name in ? ", []string{"name1", "name2"}).Group("users.id")

Comment From: the25x8

You may fetch users by group names very easily:

db.Preload("Groups").Where("id IN (SELECT user_id FROM users_groups WHERE name IN ?)", []string{"name1", "name2"})

or more strict way:

db.Where("id IN (?)", db.Table("users_groups").
    Select("user_id").
    Where("name IN ?", []string{"name1", "name2"}),
)

Comment From: cendiastian

db.Joins("JOIN user_groups on user_groups.user_id = users.id JOIN groups on user_groups.group_id = groups.id AND groups.name in ? ", []string{"name1", "name2"}).Group("users.id")

this is works! but you need to preload before join, coz when i test your code, my many to many field still null, thanks anyway @Goxiaoy

Comment From: genofire

I got it working with INNER join.

Comment From: yuseferi

I've provided a solution with inner join : https://stackoverflow.com/questions/63475885/how-to-query-a-many2many-relationship-with-a-where-clause-on-the-association-wit/73797763#73797763

Comment From: whyayala

db.Joins("JOIN user_groups on user_groups.user_id = users.id JOIN groups on user_groups.group_id = groups.id AND groups.name in ? ", []string{"name1", "name2"}).Group("users.id")

This worked. Thanks a lot.

Comment From: itaranto

I've provided a solution with inner join : https://stackoverflow.com/questions/63475885/how-to-query-a-many2many-relationship-with-a-where-clause-on-the-association-wit/73797763#73797763

Your solution has the right approach but it has a small mistake:

 var users []Users
 db.Preload("Groups").
 Joins("inner join user_groups ug on ug.user_id = user.id").
-Joins("inner join groups g on g.id = ug.user_id").
+Joins("inner join groups g on g.id = ug.group_id").
 Where("g.name IN ?", []string{"name1", "name2"}).Find(&users)

Comment From: yuseferi

Hey @itaranto , I don't remember. it's been a long time since we are not using GORM. but thank you for your attention. btw, why do you have makefile? :D