Rails: Using will_paginate with a complex association find

Question

I'm encountering a problem with will_paginate while doing a complex find.

:photo  has_many   :tags,   :through => :tagships
:item   has_many   :photos
:photo  belongs_to :item


@photos = @item.photos.paginate :page => params[:page],
                                :per_page => 200,
                                :conditions => [ 'tags.id IN (?)', tag_ids],
                                :order => 'created_at DESC',
                                :joins => :tags,
                                :group => "photos.id HAVING COUNT(DISTINCT tags.id) = #{tag_count}"

I want to fetch all the photos who have all the tags in the tag_ids array. MySQL's IN usually does "or" searches, but I need "and". I found how to modify IN to mimic "and" behavior here and it works fine when using model.find(), also works as long as the number of records fetched is lower than my :per_page count. But if it has to paginated, the SQL that is generated is similar to:

SELECT count(*) AS count_all, photos.id HAVING COUNT(DISTINCT tags.id) = 1 AS photos_id_having_count_distinct_tags_id_1 FROM `photos`(...)

which doesn't work. Other have seen this bug and were able to move their count() out of the query, but I don't think that's possible in my case.

Is there a better way to do this search that might work with will_paginate? If its the only way to do this, I guess I should look into another pagination plugin?

Thanks!

Answer 1

FYI, here's what I finally found to fix this:

@photos = WillPaginate::Collection.create(current_page, per_page) do |pager|
    result = @item.photos.find :all, :conditions => [ 'tags.id IN (?)', tag_ids] ,:order => 'created_at DESC', :joins => :tags, :group => "photos.id HAVING COUNT(DISTINCT tags.id) = #{@tags.count}", :limit => pager.per_page, :offset => pager.offset
    pager.replace(result)

    unless pager.total_entries
      pager.total_entries = @item.photos.find(:all, :conditions => [ 'tags.id IN (?)', tag_ids] ,:order => 'created_at DESC', :joins => :tags, :group => "photos.id HAVING COUNT(DISTINCT tags.id) = #{@tags.count}").count
    end
  end

You have to manually construct the paginated set using the page number as an offset and using the tags to make a join query. Kinda clunky.

Answer 2

My first stab at this (sorry don't have time to test it right now... will update if I do) would be something like the following (added the :select and changed the :group):

@photos = @item.photos.paginate :page => params[:page],
                                :per_page => 200,
                                :select => "photos.*, COUNT(DISTINCT tags.id) AS tag_count",
                                :conditions => [ 'tags.id IN (?)', tag_ids ],
                                :order => 'created_at DESC',
                                :joins => :tags,
                                :group => "photos.id HAVING tag_count = #{tag_count}"