扶手：使用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通常不会或搜索，但我需要的和。我发现如何修改模仿和行为<一href="http://stackoverflow.com/questions/2860374/is-there-something-in-mysql-like-in-but-which-uses-and-instead-of-or">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`(...)

这是行不通的。其他已经看到了这个错误，并能够将其计数（）从查询的，但我不认为这是可能在我的情况。

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.

这篇关于扶手：使用will_paginate具有复杂的关联找到的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！