How can I limit the amount of associations loaded using pre load?

[exseniorastronaut]

I think the title says it all. How can I limit the amount of associations loaded using pre load? I understand the challenges with this and I am not sure if the current implementation of eager loading would allow for something like this.

[aarondl]

I'm trying to understand what this means exactly. Do you mean apply like a limit clause to each eager load? If so you can apply query mods to qm.Load(), see the docs.

Otherwise - I'm not sure what you mean.

[exseniorastronaut]

Hi yes, this is what I mean. Imagine the following scenario.

A User can have multiple Pictures. Imagine you want a query to return all users but with only 2 pictures for each user.

models.Users().(
 qm.Load(models.UsersRels.Pictures, qm.limit(2))
).All(ctx, exec)

The following will not return each user with maximum of 2 picture but it will limit the total amount of pictures returned from the SQL SELECT statement.

I hope that makes more sense

[aarondl]

Ah yeah. This is an unfortunate limitation of the current way eager loading is done. The entire system is slated to be rewritten eventually :(

[exseniorastronaut]

@aarondl I found a solution that works for Postgresql

// uses CTE to generate row number over a specific partition
// It returns all the fields of the table + RowNum
// tableName and partitionName should be escaped and not contain user input as it is unsafe
func RowNum(tableName, partitionName string) QueryMod {
	query := fmt.Sprintf("\"%s\" AS (SELECT *, row_number() OVER (PARTITION BY %s) AS RowNum FROM %s)", tableName, partitionName, tableName)

	return With(query)
}

And then you use it as:

models.Users(
   qm.Load(
     models.UsersRels.Pictures, 
     RowNum(models.TableNames.Pictures, models.PictureColumns.UserID), 
     Where("RowNum <= ?", 2),
   ),
).All(ctx, exec)