如何限制手臂预紧力的结果

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"`
}

Here is the two models. I want to query the Topics with each have 5 latest items.

Without the limit for items, I could do this by,db.Model(&Topic{}).Preload("Items").

When I try to add some limit conditions to items:

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

It will return 5 items in total, not 5 items for each topic.

Actual result:

"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",
                    }
                ]

Expected results:

"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",
                    }
                ]

The actual sql it generated is SELECT * FROM "item" WHERE "topic_id" IN (6,4,5) DESC LIMIT 5

It's obvious not the results I want, so how should I get the expected result with gorm?

Answer 1

For postgresql only.

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)

You could use lateral join to limit the rows for each different value. After retrieving the rows of topics, gorm then send the following query to get the related rows from 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 (?))