Unavoidable Rank N+1 Queries

[CodingAnarchy]

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]

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

[CodingAnarchy]

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]

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

[brendon]

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