#120 Association with finder_sql raises TypeError - will_paginate library

wildchild

Assigned user changed from Chris Wanstrath to Mislav

September 29th, 2007 @ 05:31 AM

Mislav

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!

September 29th, 2007 @ 11:09 AM

Mislav

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.

October 7th, 2007 @ 09:04 PM

Dmitri Zhuchkov

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.

December 31st, 2007 @ 03:18 PM

Adam Tretkowski

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

November 11th, 2008 @ 11:14 AM

Eric Mill

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.

February 5th, 2009 @ 07:33 PM

Cameron Booth

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

August 19th, 2009 @ 01:42 AM

John Wood

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).

August 27th, 2009 @ 04:38 PM

wildchild

Awesome, John.

Any status update?

January 13th, 2010 @ 05:26 PM

Kandada Boggu

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.

April 8th, 2011 @ 01:18 AM

Mislav

State changed from invalid to resolved

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

Closes #120 Association with finder_sql raises TypeError
https://github.com/mislav/will_paginate/commit/370c97b092bec657f3c4...

September 27th, 2011 @ 03:45 PM

Association with finder_sql raises TypeError

Activity