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

[lopes-gustavo]

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)

[jinzhu]

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

You need to use join in this case

[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.

[take]

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

[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