How to do pagination on multiple order by keys ?

abhiturbot commented 2 years ago

I was trying to do pagination by specifying order by clause

result, cursor, err := paginate(nextToken, limit, nil)(gormdb.Debug().Joins("Identity").Where("identity_id = ? OR identity_id IN (?)", userId, subQuery).Order("type, handle , identity_workspace.handle asc").Find(&workspaces), &workspaces)

The query formed from the above code is

SELECT "identity_workspace"."id","identity_workspace"."handle","identity_workspace"."identity_id","identity_workspace"."hive","identity_workspace"."host","identity_workspace"."database_name","identity_workspace"."version_id","identity_workspace"."created_at","identity_workspace"."updated_at","identity_workspace"."public_key","identity_workspace"."state","Identity"."id" AS "Identity__id","Identity"."type" AS "Identity__type","Identity"."handle" AS "Identity__handle","Identity"."display_name" AS "Identity__display_name" FROM "identity_workspace" LEFT JOIN "identity" "Identity" ON "identity_workspace"."identity_id" = "Identity"."id" WHERE identity_id = 'user_cxxxxxxxxx' OR identity_id IN (SELECT org_id FROM "org_member" WHERE user_id = 'user_cxxxxxxxxx' AND status = 'accepted') ORDER BY type, handle , identity_workspace.handle asc,identity_workspace.id ASC LIMIT 4

I see a weird issue, it seems the results on the first page are correct, but on the second page, it returns duplicate rows.

For example Result in the first page with a limit of 4

foo
bar
acme
tom

Second page

foo
bar
new

The second page is returning

foo
bar

which is incorrect.

pilagod commented 2 years ago

Hello @abhiturbot, nice to hear from you.

I find there are some misuses for this library:

You don't need to Find before paginating, paginator will do that for you. You can remove .Find(&workspaces) in first argument.

You should define order beside the paging key by Rule, instead of on gorm DB instance, for example:

paginator.New(
    &paginator.Config{
        Rules: []paginator.Rule{
            {
                Key: "Type",
                Order: paginator.ASC,
            },
            {
                Key: "Handle",
                Order: paginator.ASC,
            },
            {
                Key: "IdentityWorkspaceHandle",
                Order: paginator.ASC,
                SQLRepr: "identity_workspace.handle",
            },
        },
    },
)

Be careful that key name in Rule is the field name on Workspace model struct.

You can refer to usage by example section in README for more configuring details.

Hope these help. 😃

pilagod commented 2 years ago

I would close this issue first. Feel free to reopen it for further discussion 😃

pilagod / gorm-cursor-paginator

How to do pagination on multiple order by keys ? #41