search

yajra / laravel-oci8

Oracle DB driver for Laravel via OCI8
https://yajrabox.com/docs/laravel-oci8
MIT License
834 stars 237 forks source link

feat: improve pagination performance #878

Closed alexc-jeromes closed 2 months ago

alexc-jeromes commented 2 months ago

Improve pagination performance by using ROW_NUMBER() and handling default orderBys. Fix: #870

yajra commented 2 months ago

I tried the patch on my starter app and got a 904 error:

Error Code    : 904
Error Message : ORA-00904: "PERMISSIONS"."ID": invalid identifier
Position      : 77
Statement     : select * from (
                    select t1.*, row_number() over (order by "PERMISSIONS"."ID" asc) as row_num
                    from (select * from "PERMISSIONS" where "RESOURCE" in (:p0, :p1, :p2, :p3, :p4) order by "PERMISSIONS"."ID" asc) t1
                ) where row_num between 1 and 1000
Bindings      : [Users,Roles,Permissions,Authorizations,System]

Need to adjust the order clause and use the table alias t1 instead:

order by "PERMISSIONS"."ID" asc

-- TO --

order by t1."ID" asc
yajra commented 2 months ago

This will be implemented via https://github.com/yajra/laravel-oci8/pull/879. Thanks!