带Rails的Postgres窗口功能列 [英] Postgres window function column with Rails

问题描述

我想在其中一列上使用PostgreSQL的rank（）函数。

I'd like to use the rank() function PostgreSQL on one of my columns.

Character.select("*, rank() OVER (ORDER BY points DESC)")

但是由于我的表导轨中没有等级列，因此查询中不包括它。

But since I don't have a rank column in my table rails doesn't include it with the query. What would be the correct way to get the rank included in my ActiveRecord object?

推荐答案

尝试此方法：

Character.find_by_sql("SELECT *, rank() OVER (ORDER BY points DESC) FROM characters")

它应返回具有rank属性的字符对象，如 此处 。但是，这可能与数据库无关，并且如果您绕过对象，则可能会变得混乱。

it should return you Character objects with a rank attribute, as documented here. However, this may not be database-agnostic and tends to get messy if you pass around the objects.

另一种（昂贵的）解决方案是在表中添加一个rank列，并在保存或销毁记录时使回调使用.order重新计算所有记录的排名。

another (expensive) solution is to add a rank column to your table, and have a callback recalculate all records' rank using .order whenever a record is saved or destroyed.

编辑：

可以看到另一种适用于单记录查询的想法 此处

another idea suitable for single-record queries can ben seen here

这篇关于带Rails的Postgres窗口功能列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！