3

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]
Carlos Morales
  • 1,137
  • 3
  • 15
  • 38
  • I recall method includes pagination [sql-limit], at one time I had a problem similar nature. Review your sql query and pagination. – Roman Kiselenko Nov 04 '13 at 19:30
  • My query is working in mysql,well i put border 0 to check if the border is closed on the 5000 row but is not border, i checked at the end at 11000 row and it closed , what is wrong seems that it has a limit, someone had the same problem? – Carlos Morales Nov 04 '13 at 19:56
  • 1
    I found this but is for asp http://stackoverflow.com/questions/1403884/unable-to-export-5000-rows/1404046?noredirect=1#comment29393982_1404046 – Carlos Morales Nov 04 '13 at 20:09
  • @CarlitosMorales you should post your server log. If it is a timeout problem, it will show. If it is an ActiveRecord problem with generating the SQL, it will also show. Timeout depends on your server conf, so you might as well tell what environment you're working with (heroku, for instance, enforces a relatively short timeout time to encourage using workers) – m_x Nov 09 '13 at 13:55
  • okey i posted my last log – Carlos Morales Nov 11 '13 at 16:24
  • what happen when you remove pagination? so only `@policies = @search` – j03w Nov 13 '13 at 05:35
  • It exports only 5000 rows but i should have 11000 rows that's the problem. – Carlos Morales Nov 13 '13 at 14:25
  • What is the result of `@search.count`? Also, do you have a default_scope on Policy? Or using any plugins/gems/unusual stuff in Policy or in general? – antinome Nov 13 '13 at 18:35
  • Also, check that your rails app is querying the same database that you connected to in the mysql console. Also, you could try `ActiveRecord::Base.connection.select_all('select .........')` as a test. – antinome Nov 13 '13 at 18:37
  • i used ".count" and got 11,408 – Carlos Morales Nov 13 '13 at 18:46
  • How can i do ActiveRecord::Base.connection.select_all('select .........') as a test ???? where is that? – Carlos Morales Nov 13 '13 at 18:47
  • @Carlitos - my select_all suggestion is probably no longer needed - it was an alternate way of querying, in case ActiveRecord was filtering behind the scenes, but since you are getting the expected 11408 records, it looks like an export problem rather than an ActiveRecord problem. – antinome Nov 13 '13 at 19:24
  • Maybe take a look at which rows are exported and which aren't. Is it exporting the _first_ 5078 records or is it going through them all and skipping certain ones? Maybe in some records num_policy is blank or null or some "bad" value that's not showing up? (I'm just guessing but those are the sorts of things I'd start looking at...) – antinome Nov 13 '13 at 19:30
  • Yes i thought that there were NUll values but i have values and is not exporting . I tried <% @results.find_each(:batch_size => 1000) do |policy| %> <%= policy.num_policy %> <% end %>..................but i got i got "undefined method `find_each' i feel that is the answer but is not working find_each – Carlos Morales Nov 13 '13 at 19:45

4 Answers4

4

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 %>
Chen
  • 578
  • 2
  • 9
  • I tried your code but i'm using rails 2.3 compile error /home/carlos/proyectos/company/app/views/policy_management/policy/_report_by_ejecutive.erb:27: syntax error, unexpected ':', expecting ')' ...@results.find_each(batch_size: 1000) do |policy| ; @output_b... ^ /home/carlos/proyectos/company/app/views/policy_management/policy/_report_by_ejecutive.erb:68: syntax error, unexpected kENSURE, expecting $end – Carlos Morales Nov 11 '13 at 15:03
  • oops, are you using ruby < 1.9 ? then you must use :batch_size => 1000. please read my updated answer – Chen Nov 11 '13 at 21:07
  • I tried your code and now i got "undefined method `find_each' for # " – Carlos Morales Nov 11 '13 at 21:15
3

It could be because you're paginating the results (which applies a scope on the larger result set). Can you try removing the pagination?

Musannif Zahir
  • 3,001
  • 1
  • 21
  • 31
  • @CarlitosMorales The pagination doesn't order the results - It just gives you enough results for each page (which makes sense for the web but not for csv exports). The ordering is handled by your SQL `ORDER BY state ASC`. Please update the question with pagination removed - there is probably a reference to it left either in the view or controller. – Musannif Zahir Nov 05 '13 at 08:27
  • Can you try removing `:page => params[:page]` from `link_to Export Excel`? – Musannif Zahir Nov 07 '13 at 04:25
2

Try to narrow down where the issue lies.

For example, you haven't mentioned if the limitation only happens when generating the Excel format. How many rows do you get when requesting the page in HTML format?

You can also check how many objects are created from the query by going to the console and doing this:

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

and check the log what number it outputs.

Next check the log . It will show you what database query is actually executed. If there is a pagination gem or some configuration would be influencing the outcome, then you'd see a different sql query that is actually executed.

UnSandpiper
  • 526
  • 6
  • 8
1

have you try return csv record first? i think maybe this is relative to the excel exporter, there maybe a limit on how many records in a single excel sheet.

nickcen
  • 1,682
  • 10
  • 10