Ruby on Rails/will_paginate:按自定义列排序 [英] Ruby on Rails/will_paginate: Ordering by custom columns
问题描述
我有一个相当复杂的 SQL 查询,它的结果应该分页并显示给用户.我不想在这里详细介绍,但简单地说,我只选择模型的所有列,和一个附加列,仅用于排序目的.
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(...)...
在一些 .join() 的
和一个 .group()
之后,我终于调用了 .order()
和 .paginate
在关系上.如果我按模型的任何自然列排序,一切正常,但如果我按人工"列 x
排序,rails 会给我错误:
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
这似乎是因为 will_paginate 似乎在获取实际数据之前执行了 COUNT(*)
语句,只是为了获取它必须处理的数据量.这个 COUNT(*)
语句(当然)是从原始语句生成的,其中包括 ORDER BY x
.现在的问题是,will_paginate 将 ORDER BY
保留在语句中,但只是用 COUNT(*)
替换了列定义,因此找不到列 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
.
使用Rails 3
和will_paginate 3.0.pre2
.
有什么办法可以解决这个问题吗?
Is there any way to get around this?
感谢您的帮助
推荐答案
您可以通过手动传入值来禁用初始计数查询:
You can disable the initial count query by passing in the value manually:
total_entries = Note.select(...).joins(...).count
Note.select( ... ).joins(...).group().paginate(:total_entries => total_entries)
以上不是文字代码示例,您可能需要调整计数查询以获得正确的连接结果.这应该可以让您对分页进行直接复杂的查询.
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.
这篇关于Ruby on Rails/will_paginate:按自定义列排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!