search

brendon / ranked-model

An acts_as_sortable/acts_as_list replacement built for Rails 4+
https://github.com/mixonic/ranked-model
MIT License
1.09k stars 134 forks source link

Unavoidable Rank N+1 Queries #196

Closed CodingAnarchy closed 5 months ago

CodingAnarchy commented 1 year ago

Currently it is impossible to avoid N+1 queries over a partitioned rank ordering. The following ranking, for instance, does not enable querying with a pre-populated rank value:

class Duck < ActiveRecord
   ranks :pool_order, with_same: :pool_id
end

SQL allows for queries that could pre-populate this value, though. We could do something like:

 RANK() OVER(PARTITION BY pool_id ORDER BY pool_order ASC) as pool_order_rank

I believe a PARTITION BY statement could be supplied for each possibility of complex ranking (for simple ranking, it is unnecessary), but I haven't looked into it completely.

brendon commented 1 year ago

Hi @CodingAnarchy, can you give a quick example? I'm not too familiar with that SQL so an example would be helpful :D

CodingAnarchy commented 1 year ago

I'm not sure what kind of example would be most helpful here, but now that I am considering it more, I know that window functions are not supported by some common older MySQL versions (I think they added it in 8.0). I believe Postgresql has had the concept since version 11. So this might be a touch premature, as we will need these older versions to go EOL and be phased out by most users to avoid hacky workarounds.

On the subject of window functions as a whole, the linked documentation might help familiarize the concept, though.

brendon commented 1 year ago

Thanks @CodingAnarchy, let's leave this here for posterity :)

brendon commented 5 months ago

Closing this for now. Have a look at my new positioning gem. If you ever end up using it, I'd be interested in seeing if we could implement support for the there :)

https://github.com/brendon/positioning