GORM: filter entity by linked entity

Take a look to a following entities:

type Parent struct {
	gorm.Model
	Name string
}

type Child struct {
	gorm.Model
	ParentID uint // child -> parent many to one link
	Name     string
}

Task is how do i filter parents by filtering linked children?

The general solution in SQL-related databases is to use simple JOIN and then filter. It is possible here with GORM:

err := db.Model(&Parent{}).Joins(
	"LEFT JOIN children ON parents.id = children.parent_id AND children.name = ?",
	"somename",
).Find(&results).Error

Full query:

SELECT * FROM "parents" LEFT JOIN children ON parents.id = children.parent_id AND children.name = 'somename'

But this is not generally working in our task — for parents who have several matching children. In that case we'll have such parents repeated multiple times.

So, probably simple JOIN is not working here. Probably we need some solution, based on where condition. Following code works perfectly:

err := db.Model(&Parent{}).Where(
	"EXISTS (SELECT 1 FROM children WHERE children.parent_id = parents.id AND children.name = ?)",
	"somename",
).Find(&results).Error

Full query looks as following:

SELECT * FROM "parents" WHERE EXISTS (SELECT 1 FROM children WHERE children.parent_id = parents.id AND children.name = 'somename')

Related articles

• Filtering by a list of values with GORM