Sod will_paginate library

Comments and changes to this ticket

• You flagged this item as spam.
  

  wildchild 2007-09-29 05:31:17 UTC

  • Assigned user changed from “Chris Wanstrath” to “Mislav”
• 

  Mislav 2007-09-29 11:09:56 UTC

  • State changed from “new” to “open”
  • Title changed from “Paginate association with finder_sql raises TypeError” to “Association with finder_sql raises TypeError”

  Damn. Gotta investigate how this should/could work. Thanks for the report!

• 

  Mislav 2007-10-07 21:04:20 UTC

  • State changed from “open” to “invalid”

  I've given it a go, but it seems too hard to support right now. Fixing ActiveRecord would be involved and I'm not sure if I should do it.

  So, if you have manual finders and counters for your associations you'll also have to do the pagination manually. WillPaginate::Collection can help you with calculating limits and offsets.

• You flagged this item as spam.
  

  Dmitri Zhuchkov 2007-12-31 15:18:20 UTC

  I hit the same problem today. It seems I was able to work around the problem by something like this:

  class Post < ActiveRecord::Base
    has_many :comments,
             :finder_sql => 'SELECT * FROM comments WHERE comments.post_id = #{id}'
             :counter_sql => 'SELECT COUNT(*) FROM comments WHERE comments.post_id = #{id}' do
      def find(*args)
        options = args.extract_options!
        sql = @finder_sql
        
        sql += sanitize_sql [" LIMIT ?", options[:limit]] if options[:limit]
        sql += sanitize_sql [" OFFSET ?", options[:offset]] if options[:offset]
        
        Comment.find_by_sql(sql)
      end
    end
  end
  

  When you make a call to

  @post.comments.paginate(:page => params[:page], :per_page => 10)
  

  then custom find method will be called which will update the finder SQL by LIMIT and OFFSET and perform the find_by_sql.

• You flagged this item as spam.
  

  Adam Tretkowski 2008-11-11 11:14:29 UTC

  • Tag set to “will_paginate”

  In my model only some relationships use finder_sql so I modified a bit your solution to make possible to chose which finder should be used and to make it accessible for all models.

  In the plugin's finder.rb file I introduced 'custom_sql' option to indicate that find_by_sql should be used

  
     WillPaginate::Collection.create(page, per_page, total_entries) do |pager|
            count_options = options.except :page, :per_page, :total_entries, :finder
            find_options = count_options.except(:count).update(:offset => pager.offset, :limit => pager.per_page)
  
            if options[:custom_sql].nil?
              args << find_options
              # @options_from_last_find = nil
  
              pager.replace send(finder, *args, &block)
            else
              sql = @finder_sql
              sql += sanitize_sql [" LIMIT ?", pager.per_page]
              sql += sanitize_sql [" OFFSET ?", pager.offset]
  
              pager.replace send("find_by_sql", sql, &block)
            end
  
            # magic counting for user convenience:
            pager.total_entries = wp_count(count_options, args, finder) unless pager.total_entries
          end
  

  Probably it is not perfect but works for me.

  @mails = @user.inbox.paginate :page => page, :order => "sent_at DESC", :per_page => 10, :custom_sql => true

• You flagged this item as spam.
  

  Eric Mill 2009-02-05 19:33:07 UTC

  Dimitri and Adam - those are awesome solutions, thank you. I was in the same situation.

  Mislav, this would be an awesome thing to build into WillPaginate.

• You flagged this item as spam.
  

  Cameron Booth 2009-08-19 01:42:35 UTC

  I might have found a cleaner solution, build off of what you've got Adam, combined with a few other hints. This appears to be working, though I have only given it cursory tests.

  I took what you did Adam, except instead of passing in ":custom_sql => true", I simply replaced the "if options[:custom_sql].nil?" with "if proxy_reflection.options[:finder_sql].nil?"

  Like I said, seems to be working so far, I'll report back if I find any issues with it

• You flagged this item as spam.
  

  John Wood 2009-08-27 16:38:06 UTC

  Hi everybody. Thanks for providing a solution to this issue.

  I took the code you guys put together here, committed it to my will_paginate fork, and submitted a pull request. My fork can be found at http://github.com/jwood/will_paginate/tree/master, in case you're interested. It uses Cameron's version with one small modification, checking to see if the object responds to proxy_reflection before calling it (since it's Rails only).

• You flagged this item as spam.
  

  wildchild 2010-01-13 17:26:45 UTC

  Awesome, John.

  Any status update?

• You flagged this item as spam.
  

  Kandada Boggu 2011-04-08 01:18:12 UTC

  • Assigned user cleared.
  • Milestone order changed from “0” to “0”

  I tried John's solution, and the exception went away. I noticed that total_entries didn't return the expected results. I have modified the fix to address this issue.

  module WillPaginate
    module Finder
      module ClassMethods
        def paginate_with_finder_sql(*args)
          if respond_to?(:proxy_reflection) && !proxy_reflection.options[:finder_sql].nil?
            # note: paginate_by_sql ignores the blocks. So don't pass the block
            paginate_by_sql(@finder_sql, args.extract_options!) 
          else
            paginate_without_finder_sql(*args)
          end
        end
        # patch to deal with the custom_sql scenario
        alias_method_chain :paginate, :finder_sql
      end
    end
  end
  

  PS: It looks like this issue is fixed in will_paginate 3.x.pre. I can't move to the latest version of as I faced the issue on a live system. So I had to resort to monkey patching the gem.

• 

  Mislav 2011-09-27 15:45:35 UTC

  • State changed from “invalid” to “resolved”

  (from [370c97b092bec657f3c494f8d027a58f3245fab4]) Active Record: fix stripping ORDER clause in paginate_by_sql

  Closes #120
  https://github.com/mislav/will_paginate/commit/370c97b092bec657f3c4...