在活动记录查询时间的优先级 [英] 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有一个功能齐全的 布尔 类型。您可以使用它的直接的在 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.

为布尔值排序顺序是：

FALSE -> TRUE -> NULL

如果您 ORDER BY bool_ex pression DESC ，您反转为了：

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 只有在设置了功能或 created_at 的可以的是 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 声明，或者您也可以在<一个扔href="http://www.postgresql.org/docs/current/interactive/functions-conditional.html#FUNCTIONS-NULLIF"相对=nofollow> NULLIF（） 或<一href="http://www.postgresql.org/docs/current/interactive/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL"相对=nofollow> 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'

在第一个例子中，前pression向右是计算，一个恒定的一旦的。然后，索引可以用来查找匹配的行。非常有效的。

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.

，后者通常不能使用的索引。一个值来计算的每一行，才可以对不断EX pression向右进行检查。不这样做，如果你能避免它。永远！

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!

这篇关于在活动记录查询时间的优先级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！