Compare values in a Many2Many relationship

[NefixEstrada]

Hello!

First of all we'd like to thank you for your hard work maintaining this awesome library!

We're trying to use go-pg for the new version of IsardVDI, but we don't know how to compare values in a Many2Many relationship. Given the following code:

func init() {
	orm.RegisterTable(&UserToEntity{})
}

type User struct {
	ID   int
	Entities []*Entity  `pg:"many2many:user_to_entity"`
	Name    string `pg:",notnull"`
        GPU int
}

type Entity struct {
        ID int
        Users []*User `pg:"many2many:user_to_entity"`
        Name string
        GPU int
}

type UserToEntity struct {
	UserID   int     `pg:",pk"`
	User     *User   `pg:"rel:has-one"`
	EntityID int     `pg:",pk"`
	Entity   *Entity `pg:"rel:has-one"`
}

We know how to filter columns of each table:

	if err := db.Model(u).Relation("Entities", func(q *orm.Query) (*orm.Query, error) {
	 	return q.Where("gpu = 0"), nil
	 }).Where("gpu = 0").Select(); err != nil {
		panic(err)
	}

But we can't find the way to compare GPU values between them, e.g.:

	if err := db.Model(u).Relation("Entities").
		Where("user.gpu <= entity.gpu").Select(); err != nil {
		panic(err)
	}

This doesn't seem to work, is there a way or something that we're missing out?

Thanks a lot!

===============
Néfix Estrada
IsardVDI Developer

[vmihailenco]

Hello,

You could try something like this

if err := db.Model(u).
	Relation("Entities", func(q *orm.Query) (*orm.Query, error) {
		q = q.Join("JOIN users AS user ON user.id = user_to_entity.user_id").
			Where("user.gpu <= entity.gpu")
		return q, nil
        }).
	Select(); err != nil {
	panic(err)
}

But overall pre-defined Relations are not very flexible and you should not rely on them to write custom queries.