Use Pagination in Ruby when fetching data with a SQL query (mysql2 type)

Question

I have a query in my controller which looks like this:

@temp = connection.execute("select test_id from mastertest limit 500;")

Now I display the results fetched here in a table in my view. But I want to limit the results to say 10 rows per page. I know I can use will_paginate and kaminari

https://github.com/amatsuda/kaminari

https://github.com/mislav/will_paginate

But can anyone give me the exact syntax or a sample code to apply pagination in my case of an SQL query and how I can display results in the view.

Answer 1

If you want pagination using sql query, then you need to use mysql offset keyword with a limit clause. So you will get exact pagination like results. I hope below code will help you.

current_page = current_page || 1
per_page = 10
records_fetch_point = (current_page - 1) * per_page

query = "select test_id from mastertest limit #{per_page} 
         offset #{records_fetch_point};"

@temp = connection.execute(query)

Answer 2

Can you not just use mastertest model? instead of connection.execute
(models are in app/models)

#using kaminari
relation = Mastertest.order(:id).limit(500).page(1).per(10)

relation.each do |row|
  puts row.test_id
end