我在进行复杂的查找时遇到了 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
发布评论