Order by association count

[marcantoinegodde]

Hello there,

It may be an already asked question but I couldn't find any answer.

type Collection struct {
	gorm.Model
	Name  string
	Items []Item
}

type Item struct {
	gorm.Model
	CollectionID uint
	Name         string
}

Basically, how would you retrieve the N collections with the most items ?
Thanks :)

[tamish-max]

You usually solve this with a LEFT JOIN + GROUP BY + ORDER BY COUNT(...) DESC.

SQL-wise it is basically:

SELECT collections.*
FROM collections
LEFT JOIN items ON items.collection_id = collections.id
GROUP BY collections.id
ORDER BY COUNT(items.id) DESC
LIMIT ?;

In GORM, something like this:

var collections []Collection

err := db.Model(&Collection{}).
  Select("collections.*, COUNT(items.id) AS items_count").
  Joins("LEFT JOIN items ON items.collection_id = collections.id").
  Group("collections.id").
  Order("items_count DESC").
  Limit(n).
  Find(&collections).Error

If you also want the count back in Go, scan into a DTO/view model instead of plain []Collection, e.g. a struct with ItemsCount int64.

One portability note: depending on the database, you may need to group by every selected non-aggregated column (or do it via a subquery and then join back).

[akashrtd]

you can do this with a subquery count in your order clause. gorm lets you sort by a raw expression:

var top []Collection
db.Order("(SELECT COUNT(*) FROM items WHERE items.collection_id = collections.id) DESC").
    Limit(10).
    Find(&top)

if you'd rather do it with a join and group by (handy if you also want the count back), use a struct with an extra field and a plain Joins/Group:

type CollectionWithCount struct {
    Collection
    ItemCount int `gorm:"column:item_count"`
}

var top []CollectionWithCount
db.Model(&Collection{}).
    Select("collections.*, COUNT(items.id) AS item_count").
    Joins("LEFT JOIN items ON items.collection_id = collections.id").
    Group("collections.id").
    Order("item_count DESC").
    Limit(10).
    Scan(&top)

the join version is usually faster if items.collection_id is indexed, since the db can do one pass instead of running a subquery per collection. the subquery version is simpler to read when you don't need the count value returned.

for either one, make sure you have an index on items.collection_id or it'll table scan.

ref: gorm docs on advanced query / subqueries and joins.