Rails:将 will_paginate 与复杂的关联查找一起使用 [英] Rails: Using will_paginate with a complex association find

问题描述

我在进行复杂的查找时遇到了 will_paginate 的问题.

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}"

我想获取在 tag_ids 数组中包含所有标签的所有照片.MySQL 的 IN 通常进行或"搜索，但我需要和".我找到了如何修改 IN 以模仿和"行为 here 并且它在使用 model.find() 时工作​​正常，只要获取的记录数低于我的 :per_page 计数也可以工作.但是如果一定要分页，生成的SQL类似:

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

这不起作用.其他人已经看到了这个错误，并且能够将他们的 count() 移出查询，但我认为在我的情况下这是不可能的.

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.

有没有更好的方法来进行这种搜索，可以与 will_paginate 一起使用?如果这是唯一的方法，我想我应该研究另一个分页插件?

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?

谢谢！

推荐答案

仅供参考，这是我最终找到的解决方案:

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.

这篇关于Rails:将 will_paginate 与复杂的关联查找一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！