我已经有工作列表,这时候显示的通常是由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_expression
DESC
, 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
声明,或者您也可以在 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'
在第一个例子中,前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!
相关推荐
最新文章