Ruby on Rails/will_paginate:按自定义列排序
我有一个相当复杂的 SQL 查询,其结果应该分页并显示给用户。我不想在这里详细介绍,但为了简单起见,我只选择模型的所有列,和附加列,仅用于排序目的。
Note.select( ['notes.*', "<rather complicated clause> AS 'x'"] ).joins(...)...
经过一些 .join()'s
和 .group()
后,我最终调用 .order()
和 .paginate< /code> 关系。如果我按模型的任何自然列排序,一切正常,但是如果我按“人工”列
x
排序,rails 会给我错误:
no such column: x
这似乎发生,因为 will_paginate 似乎做了 < code>COUNT(*) - 获取实际数据之前的语句,只是为了获取它必须处理的数据量。这个 COUNT(*)
语句(当然)是从原始语句生成的,其中包括 ORDER BY x
。现在的问题是,will_paginate 在语句中保留了 ORDER BY
,但只是用 COUNT(*)
替换了列定义,因此找不到列 x< /代码>。
使用 Rails 3
和 will_paginate 3.0.pre2
。
有什么办法可以解决这个问题吗?
感谢您的帮助
I've got a rather complicated SQL-Query whose results should be paginated and displayed to the user. I don't want to go into details here, but to put it simple I just select all columns of a model, and an additional column, that is used just for sorting purposes.
Note.select( ['notes.*', "<rather complicated clause> AS 'x'"] ).joins(...)...
After some .join()'s
and a .group()
, I finally call .order()
and .paginate
on the relation. If I order by any of the model's natural columns everything works fine, if I however order by the "artificial" column x
, rails gives me the error:
no such column: x
This seems to occur because will_paginate seems to do a COUNT(*)
-statement before getting the actual data, simply to get the amounts of data it has to process. This COUNT(*)
-statement is (of course) generated from the original statement, which includes the ORDER BY x
. The problem now is, that will_paginate keeps the ORDER BY
in the statement but simply replaces the column definitions with COUNT(*)
and thus cannot find the column x
.
Using Rails 3
and will_paginate 3.0.pre2
.
Is there any way to get around this?
thx for any help
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您可以通过手动传入值来禁用初始计数查询:
上面不是文字代码示例,您可能需要调整计数查询以获得正确的连接结果。这应该可以让您在分页上进行直接的复杂查询。
You can disable the initial count query by passing in the value manually:
The above is not a literal code sample and you may need to tweak your count query to get the correct results for your join. This should let you do a straight up complex query on the pagination.