Active Record Query中基于时间的优先级 [英] Time based priority in Active Record Query

问题描述

我有一个包含工作列表的表格，显示时通常按 created_at 字段降序排列.我正在添加一个特色"布尔标志，这将使客户能够更多地了解他们的工作列表.如果工作不到 X 天，我希望将特色列表固定在搜索结果的顶部.我将如何通过现有查询进行修改以支持这一点?

I have a table which has job listings, which when displayed are normally ordered by the created_at field descending. I am in the process of adding a "featured" boolean flag which would add the ability for customers to get more visibility to their job listing. I'd like to have the featured listings pinned to the top of the search results if the job is less than X days old. How would I modify by existing query to support this?

Jobs.where("expiration_date >= ? and published = ?", Date.today, true).order("created_at DESC") 

当前查询拉回所有当前已发布的作业，按 created_at 排序.

Current query pulls back all current, published jobs, ordered by created_at.

推荐答案

与其他一些数据库(如 Oracle)不同，PostgreSQL 有一个功能齐全的 boolean 类型.您可以在 ORDER BY 子句中直接使用它，而无需应用 CASE 语句 - 这对于更复杂的情况非常有用.

Unlike some other databases (like Oracle) PostgreSQL has a fully functional boolean type. You can use it directly in an ORDER BY clause without applying a CASE statement - those are great for more complex situations.

boolean 值的排序顺序是:

FALSE -> TRUE -> NULL

如果您ORDER BY bool_expressionDESC，则将顺序反转为:

If you ORDER BY bool_expressionDESC, you invert the order to:

NULL -> TRUE -> FALSE

如果您希望 TRUE 首先和 NULL 最后，请使用 NULLS LAST ORDER BY 的子句:

If you want TRUE first and NULL last, use the NULLS LAST clause of ORDER BY:

ORDER BY (featured AND created_at > now() - interval '11 days') DESC NULLS LAST  
       , created_at DESC

当然，NULLS LAST 仅在 featured 或 created_at can 为 NULL.如果列被定义为NOT NULL，那么就不用麻烦了.

Of course, NULLS LAST is only relevant if featured or created_at can be NULL. If the columns are defined NOT NULL, then don't bother.

此外，FALSE 将排在 NULL 之前.如果你不想区分这两者，你要么回到 CASE 声明，或者你可以加入 NULLIF() 或 COALESCE().

Also, FALSE would be sorted before NULL. If you don't want to distinguish between these two, you are either back to a CASE statement, or you can throw in NULLIF() or COALESCE().

ORDER BY NULLIF(featured AND created_at > now() - interval '11 days'), FALSE)
                                                                DESC NULLS LAST
       , created_at DESC

性能

注意，我是如何使用的:

Performance

Note, how I used:

created_at > now() - interval '11 days'

和不是:

now() - created_at < interval '11 days'

在第一个例子中，右边的表达式是一个计算一次的常数.然后可以使用索引来查找匹配的行.效率很高.

In the first example, the expression to the right is a constant that is calculated once. Then an index can be utilized to look up matching rows. Very efficient.

后者通常不能与索引一起使用.必须为每一行计算一个值，然后才能根据右侧的常量表达式对其进行检查.如果可以避免，请不要这样做.永远！

The latter cannot usually be used with an index. A value has to be computed for every single row, before it can be checked against the constant expression to the right. Don't do this if you can avoid it. Ever!

这篇关于Active Record Query中基于时间的优先级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！