时间戳

首页 > 解决方案 > 如何限制gorm预加载的结果

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

问题描述

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获得预期的结果？

标签： sqlgojoinpreloadgo-gorm

解决方案

仅适用于 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 (?))

推荐阅读