Complex pagination scenario with group by and Rails

Question

I have a table with probably 9000+ rows that I'm trying to paginate over (with Kaminari) but having an issue trying to figure out how.

In my scenario, I have an e-commerce store and my customers can request to have funds for an order transferred to their PayPal.

My models are as follows:

# customer.rb

has_many :transfers, through: :orders

# order.rb

belongs_to :customer
has_one :transfer

# transfer.rb

belongs_to :order
belongs_to :customer

I'm trying to paginate through all of the transfers, but grouped by the customer. I can do this currently by:

# transfers_controller.rb

@transfers = Transfer.includes(:order, :customer)

and then looping over them in my view like:

# index.html.erb

@transfers.group_by(&:customer).each

This is working great, I can loop over my transfers grouped by customer. That is, until I run into the issue of displaying a large amount of results and thus needing to paginate.

I can easily of course paginate with Kaminari like:

@transfers = Transfer.includes(:order, :customer).page(params[:page])

The problem with this is this kinda screws with my group_by. I could be looping over a customer and maybe not all of the transfers are included because it hit the per page limit, which is misleading. In my view I need to make sure that when I'm grouped by a customer, I'm at least seeing all of the transfers for that customer.

I know that Kaminari also has a paginate method for arrays, so I can do this:

@paginatable_array = Kaminari.paginate_array(@transfers.group_by(&:customer)).page(params[:page]).per(10)

and then in my view:

# index.html.erb

@paginatable_array.each

But the problem with this direction is I still need to query all rows initially, so ultimately the whole query has to run on the 9000+ rows so the pagination is not helping me at all.

Any help on how to paginate given this scenario so I can optimize this query/page?

UPDATE:

Not sure why I didn't think of this, brain fart? But it was easiest for me to just query by Customer instead, like so in my controller:

@customers = Customer.includes(:orders, :transfers).page(params[:page])

And then in my view:

@customers.each do |customer|
  customer.transfers.each do |transfer|
    # do stuff
  end
end

This way when I paginate, I'm paginating on customers.

Answer 1

I would sort the records by customer_id when loading from the database, that should keep records belonging to the same customer together on each page.

# in the controller
@transfers = Transfer
               .includes(:order, :customer)
               .sort(:customer_id)
               .page(params[:page])

# in the view
@transfers.group_by(&:customer).each 

Answer 2

Answered in the original question via an update to the post, but posting here too:

Not sure why I didn't think of this, brain fart? But it was easiest for me to just query by Customer instead, like so in my controller:

@customers = Customer.includes(:orders, :transfers).page(params[:page])

And then in my view:

@customers.each do |customer|
  customer.transfers.each do |transfer|
    # do stuff
  end
end

This way when I paginate, I'm paginating on customers and there's no chance of pagination interrupting the transfers for the customer when looping.