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屋！