Sod will_paginate library

Comments and changes to this ticket

• You flagged this item as spam.
  

  Chris Wanstrath February 16th, 2008 @ 12:29 PM

  • State changed from “new” to “open”
  • Assigned user changed from “Chris Wanstrath” to “Mislav”
• 

  Mislav February 23rd, 2008 @ 01:53 PM

  • Title changed from “select ... AS count_table doesn't work on oracle” to “count SQL invalid on Oracle”

  Hm... that query is generated by ActiveRecord count method. That means the adapter for Oracle might be broken?

  Does every pagination call fail? Can you paste me a simple example where this error occurs (the call to paginating finder and generated SQL for both select and count)?

  Thanks

• You flagged this item as spam.
  

  SideBurns February 27th, 2008 @ 12:43 AM

  I am having the same problem. Here is what I am doing in my controller:

  @resumes = EmResume.paginate_by_sql ['select * from em_resumes where is_active = ?',1], :page=>1

  The error I get is:

  SQL command not properly ended: SELECT COUNT(*) FROM (select * from em_resumes where is_active = 1) AS count_table

  If I use:

  @resumes = EmInternetResume.paginate :page=>1, :order=>'created_on desc'

  then it works fine.

  I will be using more complex queries that require sql.

• You flagged this item as spam.
  

  Jan Berkel February 27th, 2008 @ 12:43 PM

  try applying the patch i posted, it should work on both oracle and mysql.

  my calling code looks like this

  LoggingEvent.paginate_by_sql(["select * from logging_events where stack_trace like ? or message like ?", "%#{search_for}%", "%#{search_for}%"], :include=>'log_context', :page => params[:page], :per_page => settings.items_per_page, :order => 'timestamp asc, logging_events.id asc')

  as i said above, "AS count_table" is not valid oracle sql (which gets appended by the plugin)

• 

  Mislav February 27th, 2008 @ 01:59 PM

  • State changed from “open” to “resolved”

  oh, so this is a problem with count query generated in paginate_by_sql ...

  Well, that was bound to fail in certain adapters. The real solution is to construct your own count query, because it's very hard to do that automatically (would require parsing the original select query).

  class LoggingEvent
    def self.paginate_events(search_for, page, per_page = nil)
      search_query = ["select * from #{table_name} where stack_trace like ?", "%#{search_for}%"]
      count_query = search_query.dup
      # change the original query to count
      count_query[0] = count_query[0].sub('*', 'COUNT(*)')
      # add ORDER to original select query
      search_query.first << " ORDER BY timestamp asc"
  
      total_entries = count_by_sql(count_query)
      paginate_by_sql(search_query, :page => page, :per_page => per_page, :total_entries => total_entries)
    end
  end
  

  But, Jan, I don't think you really need paginate_by_sql here. By what you pasted it's obvious that only a `paginate` call with :conditions is sufficient.

• You flagged this item as spam.
  

  Jan Berkel February 27th, 2008 @ 05:29 PM

  ok, changed my code it works now... works fine the way you suggested, sorry about that.

• You flagged this item as spam.
  

  SideBurns February 27th, 2008 @ 08:07 PM

  I actually used the patch and it works great. I think it would be good to include the patch in future editions of will_paginate. It makes the code much simplier and cleaner.

• 

  Mislav February 27th, 2008 @ 09:36 PM

  Applied because it's not expensive.

  Note that manually-written count query will probably always perform better, though.

• 

  Mislav September 27th, 2011 @ 03:17 PM

  • Tag set to “count, oracle, sql, will_paginate”
  • Milestone order changed from “0” to “0”

  (from [9bd5aec3ddccfee211a2ef512148aaf8eeda9d5f]) Active Record: fix paginate_by_sql(:page => nil)

  Closes #187
  https://github.com/mislav/will_paginate/commit/9bd5aec3ddccfee211a2...