自定义方法上的rails gem will_paginate不起作用

Question

在我的控制器中，当我尝试在其上堆叠will_paginate时，我的filter_with_params()方法在postgres中导致语法错误。

在我的控制器中，我调用：

@styles = Style.filter_with_params(params).paginate(:page => params[:page], :per_page => 6)

模型方法：

class Style < ActiveRecord::Base

def self.filter_with_params(params)
    scoped = where("styles.item != ''")
    if params[:t]
      scoped = scoped.joins(:tags)
      scoped = scoped.select("distinct styles.*, count(*) AS count")
      scoped = scoped.where(tags: { name: params[:t] })
      scoped = scoped.group('styles.id')
      scoped = scoped.having("count(*) = #{params[:t].size}")
    end
    scoped
end

基本上，我的filter方法是查找标签，然后我需要在这些结果之上进行分页。有没有人有类似的经历？

我在这个应用上使用的是Rails 3

这里是postgres错误

PG::Error: ERROR:  syntax error at or near "distinct" LINE 1: SELECT COUNT(*) AS count_all, distinct styles.*, count(*) AS...
                                  ^
: SELECT COUNT(*) AS count_all, distinct styles.*, count(*) AS count, styles.id AS styles_id FROM "styles" INNER JOIN "tagizations" ON "tagizations"."style_id" = "styles"."id" INNER JOIN "tags" ON "tags"."id" = "tagizations"."tag_id" WHERE "tags"."name" IN ('engagement') AND (styles.polenza_item != '') GROUP BY styles.id HAVING count(*) = 1

Answer 1

您的SQL有问题。您需要在count ('count(*) as count_all')之前使用distinct子句。也就是说，一旦删除了对count函数的第一个调用，它就应该可以工作了。

SELECT distinct styles.*, count(*) AS count, styles.id AS styles_id FROM "styles" INNER JOIN "tagizations" ON "tagizations"."style_id" = "styles"."id" ...

您可以在rails控制台中测试您的查询：

>> sql = "SELECT distinct styles.*, count(*) AS count, styles.id AS styles_id..."
>> a = ActiveRecord::Base.connection.execute(sql)
>> a[0]

希望这能有所帮助。