sql - 如何限制gorm预加载的结果

Question

type Item struct {
    TopicId       int          `json:"topic_id"`
    Topic         *Topic       `json:"topic,omitempty"`
    BotId         int          `json:"bot_id"`
    URL           string       `gorm:"varchar(250);unique" json:"url"`
    Title         string       `gorm:"varchar(250)" json:"title"`
}

type Topic struct {
    Title       string      `gorm:"varchar(250)" json:"title"`
    Items       []*Item     `json:"items,omitempty"`
}

这是两个模型。我想查询每个主题都有 5 个最新项目。

没有项目限制，我可以通过 db.Model(&Topic{}).Preload("Items") 来做到这一点。

当我尝试为项目添加一些限制条件时:

db.Model(&Topic{}).Preload("Items", func(db *gorm.DB) *gorm.DB {
  return db.Order("title DESC").Limit(5)
})

它将总共返回 5 个项目，而不是每个主题返回 5 个项目。

实际结果:

"records": [
            {
                "id": 4,
                "created_on": "2019-08-11T10:28:54.910022Z",
                "title": "Topic 1",
            },
            {
                "id": 5,
                "created_on": "2019-08-11T10:29:26.952614Z",
                "title": "Programming",
            },
            {
                "id": 6,
                "created_on": "2019-08-11T10:34:16.040229Z",
                "title": "Topic 3",
                "items": [
                    {
                        "id": 1,
                        "created_on": "2019-08-27T14:23:17.766055Z",
                        "topic_id": 6,
                        "title": "Title One",
                    },
                    ......
                    {
                        "id": 5,
                        "created_on": "2019-08-27T14:23:17.766055Z",
                        "topic_id": 6,
                        "title": "Title five",
                    }
                ]

预期结果:

"records": [
            {
                "id": 4,
                "created_on": "2019-08-11T10:28:54.910022Z",
                "title": "Topic 1",
            },
            {
                "id": 5,
                "created_on": "2019-08-11T10:29:26.952614Z",
                "title": "Programming",
                "items": [
                    {
                        "id": 6,
                        "created_on": "2019-08-27T14:23:17.766055Z",
                        "topic_id": 5,
                        "title": "Title six",
                    },
                    ......
                    {
                        "id": 10,
                        "created_on": "2019-08-27T14:23:17.766055Z",
                        "topic_id": 5,
                        "title": "Title ten",
                    }]
            },
            {
                "id": 6,
                "created_on": "2019-08-11T10:34:16.040229Z",
                "title": "Topic 3",
                "items": [
                    {
                        "id": 1,
                        "created_on": "2019-08-27T14:23:17.766055Z",
                        "topic_id": 6,
                        "title": "Title One",
                    },
                    ......
                    {
                        "id": 5,
                        "created_on": "2019-08-27T14:23:17.766055Z",
                        "topic_id": 6,
                        "title": "Title five",
                    }
                ]

它生成的实际 sql 是 SELECT * FROM "item"WHERE "topic_id"IN (6,4,5) DESC LIMIT 5

显然不是我想要的结果，那么gorm应该如何得到预期的结果呢？

Answer 1

仅适用于 postgresql。

type Topic struct {
    Title string  `gorm:"varchar(250);PRIMARY KEY" json:"title"`
    // assume the foreign key between two tables are both Title.
    Items []*Item `gorm:"foreignkey:Title;association_foreignkey:Title" json:"items,omitempty"`
}

var topics []Topic
db.Model(&Topic{}).Preload("Items", func(tx *gorm.DB) *gorm.DB {
    return tx.Joins(`JOIN LATERAL (
        SELECT i.url FROM items i WHERE i.title = items.title ORDER BY i.topic_id DESC LIMIT 5
        ) AS foo ON foo.url = items.url`)
}).Find(&topics)

您可以使用横向连接来限制每个不同值的行。在检索到 topics 的行后，gorm 然后发送以下查询以从 items 中获取相关行:

SELECT "items".*
FROM "items"
JOIN LATERAL
  (SELECT i.url
   FROM items i
   WHERE i.title = items.title
   ORDER BY i.topic_id DESC
   LIMIT 5) AS foo ON foo.url = items.url
WHERE ("title" IN (?))