Unable to export more than 5000 rows in rails

Question

I did an app to export all my policies in a excel file but is not exporting all my information

Here is the query in mysql

select * from policies where deleted = 0 AND (state = 0 OR state= 1) ORDER BY state ASC 
i got 11,408 total when i executed it on mysql

Here is my controller

  @search = Policy.find_by_sql("select * from policies where deleted = 0 AND (state = 0 OR state= 1) ORDER BY state ASC ")
  @policies = @search.paginate(:page => params[:page], :per_page => 10)
  @results = @search

  respond_to do |format|
     format.html
     format.xls { send_data render_to_string(:partial=>"report"), :filename => "Report.xls" }
  end

Here is my view

 <%= link_to "Export Excel",{:controller=>"policy",:action=>"report", :format=>"xls",:page => params[:page] }  %>

Here is my partial view

<% @results.each do |policy| %>
     <%= policy.num_policy %> 
<% end %>

Actually is only exporting 5078 rows and i should have 11,408

Someone can help me with this?

I will really appreciate help

Here is my last log

 Rendered policy_management/policy/_report_by_ejecutive (42929.2ms)
 Sending data Report_2013-11-11.xls
 Completed in 43533ms (View: 0, DB: 0) | 200 OK [http://0.0.0.0/policy_management/policy/generate_print_ejecutive?format=xls]

Answer 1

You should use batch query ActiveRecord Batch query

@search = Policy.find_by_sql("select * from policies where deleted = 0 AND (state = 0 OR state= 1) ORDER BY state ASC ")

@search = Policy.where("deleted = 0 AND (state = 0 OR state = 1)").order("state ASC")

In your partial

<% @results.find_each(:batch_size => 1000) do |policy| %>
 <%= policy.num_policy %> 
<% end %>