search

felixmosh / knex-paginate

An extension of Knex's query builder with `paginate` method that will help with your pagination tasks.
MIT License
112 stars 12 forks source link

isLengthAware issue #9

Closed VitorBalotin closed 4 years ago

VitorBalotin commented 4 years ago

I'm using Oracle database and when I passed the argument 'isLengthAware' as true, it would return an error on the query I was running.

"select * from (select count(*) total from (select * from user) __count__query__) where rownum <= :1 - ORA-00911: invalid character"

I decided to take a look at the library and identified two things that caused the problem. First one was at line 44 .as('__count__query__'), and the other was at line 57 where you destructure the object to grab the total, but in my case the total was returning as upperCase. Would you mind giving me an explanation to the .as('__ count __query __'),, or maybe another solution to this problem, without modifying the library? The fix I identified was by removing the .as() and making the total upperCase, is it something that would break in other types of databases?

Let me know and I'll open a PR with my fix. Thanks!

felixmosh commented 4 years ago

Hi, thank you for using the lib.

I’m not sure what is the reason but looks like oracle db has a special meaning for __ (double underscores).

Can you show the query that you tried to build?

VitorBalotin commented 4 years ago

Of course, it's a simple select with no where clause and just a order by date, that's it, on the error itself shows the query.

felixmosh commented 4 years ago

Ok, can you change this line https://github.com/felixmosh/knex-paginate/blob/master/lib/index.js#L44 to .as('count_query_')?

VitorBalotin commented 4 years ago

Sure thing, I'll do it first thing on monday. Unfortunately I don't have access to it on the weekends.

VitorBalotin commented 4 years ago

Using .as('count_query_') works, I actually did a test by running a simple query directly on SQL developer and looks like oracle does not allow underscores in the beginning of the identifier, it has to be a letter or number. Only other issue is the total as lower case, it does not work with it like that.

felixmosh commented 4 years ago

Thank you for returning back. "oracle does not allow underscores"- as I've suspected...

I'm not sure regarding the lower case total, is oracle enforces uppercase for column names?

VitorBalotin commented 4 years ago

Not sure either, but everything that uses knex, returns as upper case, even when i assign it to return as lower case, it returns as upper case. Other query's that use a different library, when we specify the return, it comes as we specified(lower case, camelCase or upper case).

felixmosh commented 4 years ago

@VitorBalotin check v1.2.2, it should work :]

VitorBalotin commented 4 years ago

Thanks @felixmosh , it's all working!