Gorm Nested preload with limit

Your Question

I want to perform a limit on a nested preload to improve performance. The limit works on the total count on the children but I want the limit to work on each distinct owner.

The models are:

type User struct {
    ID                 uint64   
    Fullname           string     
    Email              string    
        Work               []Work  
}

type Work struct {
    ID           uint64       
    CompanyName  string      
    UserID       uint64       
    User         *User       //  belongs to is needed here
    Project      []Project     
}
type Project struct {
    ID          uint64        
    Role        string  
    Description string   
    WorkID      uint64       
}

Query without Limit:

db.Where("email = ?","myemail@mail.com").
        Preload("Work",  func(db *gorm.DB) *gorm.DB {
            return db.Preload("Project").
                Order("works.id DESC")
        }).
        Find(&user)

Result:

"user": {
        "fullname": "Beyond Beyond",
        "email": "mrbeyond4@gmail.com",
        "works": [
                  {
                 "company_name": "Paycoins",
                  "user": null,
                 "projects": [
                     {
                    "role": "Full stack engineer",
                     "description": "Client app for cryptocurrency payment",
                 },
                    {
                    "role": "Full stack engineer",
                     "description": "Checkout app for cryptocurrency payment",
                   },
                   {
                     "role": "Full stack engineer",
                     "description": "Web hook plugin for api integration",
                  }
                  ]
           },
               {
                  "company_name": "Medillery",
               "user": null,
                "projects": [
                     {
                     "role": "Full stack engineer",
                     "description": "Client app for creating and assigning projects",                       
                     },
                    {
                    "role": "Fronted engineer",
                        "description": "Client app for creating and assigning projects",
                    }
                    ]
           }
         ],
         }

Query used to Limit Projects assocatied with Work to just one:

db.Where("email = ?","myemail@mail.com").
        Preload("Work",  func(db *gorm.DB) *gorm.DB {
            return db.Preload("Project",  func(pjdb *gorm.DB) *gorm.DB {
                return pjdb.Limit(1).
                       Order("projects.id DESC")
                 } ).
                Order("works.id DESC")
        }).
        Find(&user)
 ```  
Result: 

```json
"user": {
"user": {
        "fullname": "Beyond Beyond",
        "email": "mrbeyond4@gmail.com",
        "works": [
                  {
                 "company_name": "Paycoins",
                  "user": null,
                 "projects": [
                     {
                    "role": "Full stack engineer",
                     "description": "Client app for cryptocurrency payment",
                 },
                    {
                    "role": "Full stack engineer",
                     "description": "Checkout app for cryptocurrency payment",
                   },
                   {
                     "role": "Full stack engineer",
                     "description": "Web hook plugin for api integration",
                  }
                  ]
           },
               {
                  "company_name": "Medillery",
               "user": null,
                "projects": [  ]  ## Issue No project fetched here due to limit 1, I want a single project fetched here as well.
           }
         ],
         }

The Issue:

It limits total project fetched to just one instead one to each work.

Expected answer:

"user": {
        "fullname": "Beyond Beyond",
        "email": "mrbeyond4@gmail.com",
        "works": [
                  {
                 "company_name": "Paycoins",
                  "user": null,
                 "projects": [
                     {
                    "role": "Full stack engineer",
                     "description": "Client app for cryptocurrency payment",
                 }
                  ]
           },
               {
                  "company_name": "Medillery",
               "user": null,
                "projects": [
                     {
                     "role": "Full stack engineer",
                     "description": "Client app for creating and assigning projects",                       
                     }
                    ]
           }
         ],
         }

I seriously need help with this.

Thanks.

Comment From: github-actions[bot]

This issue has been automatically marked as stale because it has been open 360 days with no activity. Remove stale label or comment or this will be closed in 180 days

Comment From: mnussbaum

This is still an active issue for me

Comment From: a631807682

First of all, before the latest version, we do not support nested preload, and secondly, the currently supported method should be

Preload("Work", func(db *gorm.DB) *gorm.DB {
                    ...
}). Preload("Work.Project")

https://github.com/go-gorm/gorm/pull/6137

cc @black-06

Comment From: mnussbaum

@a631807682 thanks for the pointer to that PR! Does the new syntax allow a limit to be applied to the Project in Work.Project?

Comment From: black-06

DB.Where("email = ?", "user").
    Preload("Work").
    Preload("Work.Project", func(tx *gorm.DB) *gorm.DB {
        return tx.Limit(1).Order("projects.id DESC")
    }).
    Find(&rst).Error

SQL is

SELECT * FROM `projects` WHERE `projects`.`work_id` IN (1,2) ORDER BY projects.id DESC LIMIT 1
SELECT * FROM `works` WHERE `works`.`user_id` = 1
SELECT * FROM `users` WHERE email = "user"

But it only has one Project in all Works.

---

Try group by work_id:

DB.Debug().
    Where("email = ?", "user").
    Preload("Work").
    Preload("Work.Project", func(tx *gorm.DB) *gorm.DB {
        return tx.Group("work_id")
    }).
    Find(&rst).Error
// SELECT * FROM `projects` WHERE `projects`.`work_id` IN (1,2) GROUP BY `work_id`

Comment From: a631807682

It seems that we have no way to set the limit number of each subset in a query, @mnussbaum what is the raw sql you want?