How to query a many2many relationship with a Where clause on the association with go-gorm?

Question

I have the following entities:

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

type Groups 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)

I opened an issue on gorm repository as well

user_8274750 · Answer

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, a more strict way:

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

user_1381737 · Answer

I had the same problem and I solve it with using Join.
Probably it's late for your problem but this could help folks when they have the same problem.

solution for your case would be :

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 ").
Where("g.name IN ?", []string{"name1", "name2"}).Find(&users)

ref: https://gorm.io/docs/query.html

user_14171386 · Answer

you can use:

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

(edited) this is also just filter the Groups, not the users (by there groups)

user_18378043 · Answer

Actually I did came up with a solution of querying a gorm model containing 3-4 many 2 many relationships with filters for main model as well as filters for fields inside association. Here filters type is map[string]string noOfAssociationFilters stores how many associations we have found as for the first association our col name will be id (field in Lawyer Model) and for other nested queries it will be lawyer_id (field name in associations).

var lawyers []models.Lawyer
var noOfAssociationFilters int = 0
var statement string = "SELECT * FROM lawyers WHERE 1=1"
if filters["experience"] != "" {
    statement += " AND `experience` = " + filters["experience"]
}
if filters["location_id"] != "" {
    statement += " AND `location_id` = " + filters["location_id"]
}
if filters["gender"] != "" {
    statement += " AND `gender` = \"" + filters["gender"] + "\""
}
if filters["practice_area_id"] != "" {
    colName := func() string {
        if noOfAssociationFilters == 0 {
            return " `id`"
        } else {
            return " `lawyer_id`"
        }
    }()
    statement += " AND" + colName + " IN ( SELECT `lawyer_id` FROM lawyer_practice_areas WHERE practice_area_id = " + filters["practice_area_id"]
    noOfAssociationFilters += 1
}
if filters["court_id"] != "" {
    colName := func() string {
        if noOfAssociationFilters == 0 {
            return "`id`"
        } else {
            return "`lawyer_id`"
        }
    }()
    statement += " AND" + colName + " IN ( SELECT `lawyer_id` FROM lawyer_courts WHERE court_id = " + filters["court_id"]
    noOfAssociationFilters += 1
}
if filters["language_id"] != "" {
    colName := func() string {
        if noOfAssociationFilters == 0 {
            return "`id`"
        } else {
            return "`lawyer_id`"
        }
    }()
    statement += " AND" + colName + " IN ( SELECT `lawyer_id` FROM lawyer_languages WHERE language_id = " + filters["language_id"]
    noOfAssociationFilters += 1
}
for i := 0; i < noOfAssociationFilters; i++ {
    statement += ")"
}
statement += ";"
fmt.Println(statement)
result := db.Raw(statement).Find(&lawyers)
fmt.Println("RowsAffected", result.RowsAffected)

I have tested the solution and it was working fine for me. It generates the complete statement you need in the end based upon your filter requirements. If you pass a filter then it generates a statement for that. It generates statements like these:-

SELECT * FROM lawyers WHERE 1=1 AND experience = 5 AND location_id = 4 AND gender = "female" AND id IN ( SELECT lawyer_id FROM lawyer_practice_areas WHERE practice_area_id = 3 ANDlawyer_id IN ( SELECT lawyer_id FROM lawyer_courts WHERE court_id = 3));
SELECT * FROM lawyers WHERE 1=1 AND experience = 5 AND location_id = 4 AND gender = "female";

By this logic we can generate nested queries and get required data for m2m relations.

For reference this was my Lawyer Model

type Lawyer struct {
ID               uint                 `gorm:"primaryKey" json:"id"`
FirstName        string               `gorm:"type:varchar(100) not null" json:"first_name"`
LastName         string               `gorm:"type:varchar(100) not null" json:"last_name"`
FullName         string               `gorm:"->;type:text GENERATED ALWAYS AS (concat(first_name,' ',last_name)) VIRTUAL;" json:"full_name"`
LocationID       uint                 `gorm:"not null" json:"location_id"`
Location         Location             `gorm:"foreignKey:location_id" json:"location"`
Email            string               `gorm:"unique;not null" json:"email"`
Phone            string               `gorm:"type:varchar(100);not null" json:"phone"`
Password         string               `gorm:"type:varchar(100);not null" json:"password"`
ImageURL         string               `gorm:"type:text" json:"image_url"`
Education        string               `gorm:"not null" json:"education"`
Experience       uint                 `gorm:"not null" json:"experience"`
Verified         bool                 `gorm:"not null" json:"verified"`
Gender           string               `gorm:"not null" json:"gender"`
Reviews          []Review             `gorm:"foreignKey:LawyerID" json:"reviews"`
Courts           []Court              `gorm:"many2many:lawyer_courts" json:"courts"`
Languages        []Language           `gorm:"many2many:lawyer_languages" json:"languages"`
PracticeAreas    []LawyerPracticeArea `gorm:"foreignKey:LawyerID" json:"practice_areas"` }

And one of the many2many association was of this type

type LawyerPracticeArea struct {
ID             uint         `gorm:"primaryKey" json:"id"`
LawyerID       uint         `gorm:"not null" json:"lawyer_id"`
Lawyer         Lawyer       `gorm:"foreignKey:LawyerID" json:"lawyer"`
PracticeAreaID uint         `gorm:"not null" json:"practice_area_id"`
PracticeArea   PracticeArea `gorm:"foreignKey:PracticeAreaID" json:"practice_area"`
Charge         int          `gorm:"" json:"charge"`
CreatedAt      time.Time    `json:"created_at"`
UpdatedAt      time.Time    `json:"updated_at"` }

Upvote if you found this helpful :) !!

How to query a many2many relationship with a Where clause on the association with go-gorm?

4 Answers