GORM: filter entity by linked entity

March 30, 2020 Gorm Sql Postgresql

This task is not that simple, especially with GORM.

Take a look at the following entities:

type Parent struct {
gorm.Model
Name string
}

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

How do I filter parents by filtering linked children?

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

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

The full query:

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

But this does not generally work for our task — for parents who have several matching children. In that case we’ll have such parents repeated multiple times.

So, a simple JOIN probably does not work here. We probably need a solution based on a WHERE condition. The 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

The full query looks as follows:

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

Test Your Knowledge

1. Why doesn't a simple JOIN work for filtering parents by linked children when parents have multiple matching children?
2. What is the recommended solution for filtering entities by linked entities in GORM?
3. What does the EXISTS subquery return?

Related Articles

April 7, 2020

Filtering by a list of values with GORM

When you need to filter data by a list of values (for example, IDs: 1, 2, 3), you should use the ANY operator combined with pq.Array from the PostgreSQL driver.

Read More → Gorm Sql Postgresql