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')
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.