Rails:将nils排序到作用域的末尾? [英] Rails: Sort nils to the end of a scope?

问题描述

因此，我的照片模型具有以下作用域:

So, I have the following scope in my Photo model:

scope :best, order(:average_rating.desc)

唯一的问题是，事实发生后将评级添加到模型中，因此生产应用程序具有很多记录，其中average_rating为零.当我将此作用域称为范围"时，它首先返回所有nil-实际上应该相反，"nil"应该最后(它们是尚未评级的照片).

The only problem is, the ratings were added to the model after the fact, so the production app has a lot of records where average_rating is nil. When I call this scope it returns all the nils first -- in fact it should be the opposite, nils should be last ( they are photos which have not yet been rated ).

如何将nils排序到此作用域的末尾?

How can I sort nils to the end of this scope?

推荐答案

我对游戏有点迟了，但这又来了，解决方案的确不是那么困难.

I'm a bit late to the game but this just came up again and the solution really isn't that difficult.

一种可移植的解决方案是使用CASE将NULL转换为结尾.如果您的评分为非阴性，则-1是一个不错的选择:

A portable solution is to use a CASE to turn NULLs into something that will go to the end. If your ratings are non-negative, then -1 is a good choice:

order('case when average_rating is null then -1 else average_rating end desc')

使用COALESCE可以在许多数据库中工作，并且比CASE少很多噪音:

Using COALESCE instead will work in many databases and is a lot less noisy than a CASE:

order('coalesce(average_rating, -1) desc')

如果要进行ASC排序，则上述方法会将NULL放在开头.如果您最终希望使用它们，则可以使用比最高评分更高的值，而不是-1.例如，如果您将事物的评分从1到5，则可以使用11代替NULL:

If you wanted an ASC sort then the above approaches would put NULLs at the beginning. If you wanted them at the end then you'd use something bigger than your highest rating instead of -1. For example, if you rated things from one to five, you could use 11 to replace NULLs:

order('case when average_rating is null then 11 else average_rating end asc')
order('coalesce(average_rating, 11) asc')

大多数人会使用10，但有时您需要将其调大一点，所以我们才11.

Most people would use 10 but sometimes you need to get a little bit louder so ours go to 11.

您不能真正依赖于数据库在开头或结尾放置NULL，因此即使您只是在提醒自己自己已经处理了NULL情况，最好也要明确.

You can't really depend on the database putting NULLs at the beginning or end so it is best to be explicit even if you're just reminding your future-self that you've handled the NULL case already.

这篇关于Rails:将nils排序到作用域的末尾?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！