search

snelg / cakephp-3-oracle

Oracle datasource for CakePHP 3.x
Apache License 2.0
11 stars 7 forks source link

Pagination Sort #11

Open mklappen opened 8 years ago

mklappen commented 8 years ago

Hate to open another issue, but just noticed when implementing pagination in my app that the cakephp paginator sort does not seem to be passed into the Oracle SQL that's created from driver.

The initial query passed from model sorts correctly and adds the "Order By" in the inner select query as expected. But the Order By gets dropped when I try to sort by the cakePHP Paginator Sort option that's on one of the columns. Will try to trace back where it's getting dropped. .

snelg commented 8 years ago

Opening another issue is fine. I prefer that to having a single issue expand and evolve over time. I can't re-create the problem. Sorting + Pagination is working as expected on my little test system. So it's not broken at the very basic level. There could very easily still be something missing in my code, though. Some condition I didn't consider. If you can't figure out where the problem is, you can send me code examples (whatever you think is pertinent) and I'll see if I can figure it out.

mklappen commented 8 years ago

hmm, thanks. Not sure on this, does your pagination example you tested contain any relationship/foreign key? My table has a belongsTo/hasMany relationship so it's pulling a foreign key from another table/model. So there's an INNER JOIN in the generated SQL both when page loads and when I click on a column to sort. Just that after clicking on column header to sort the ORDER BY not added into the SQL.

That's only thing I can think of now, but I honestly havent had a lot of time to look into it today. Will try to debug more and let you know, but may be a week before as I'm out of town next week.

Thanks!

snelg commented 8 years ago

Ah, no I didn't test with relationships. I'll try that in a bit, but maybe you missed the "sortWhitelist" pagination thing: http://book.cakephp.org/3.0/en/controllers/components/pagination.html#control-which-fields-used-for-ordering Important quote on that page: "This option is required when you want to sort on any associated data"

snelg commented 8 years ago

Yep, pretty sure that's your problem. Sorting on related table data works properly for me, but only when using the sortWhitelist as described in that link.

mklappen commented 8 years ago

ah, yes you may be right... i don't think I did add sortWhitelist. away from computer now but will double check and post back when have access to my code. thanks again