Django排序方法详解介绍

Django的各种排序方法

今天在公司遇到一个给通知公告置顶的需求，我就学习了一下Django如何进行排序

1、如何以升序或者降序方式给查询及排序

通过order_by方法实现，需要传入待排序的字段，查询语句如下

User.objects.all().order_by('pk')  # 升序，即从小到大
User.objects.all().order_by('-pk')  # 降序，即从大到小

# 你可以传入多个字段给order_by方法
User.objects.all().order_by('pk', 'create_time')

查看SQL语句

SELECT "user"."id",
       -- More fields
       "user"."create_time"
FROM "user"
ORDER BY "user"."id" ASC,
         "user"."create_time" DESC

2、如何以不区分大小写的方式排序

通过字母顺序和基于大小写排序

from django.db.models.functions import Lower
user.objects.all.order_by('username').values_list('username',flat=true)  # 区分大小写
user.objects.all.order_by(Lower(username)).values_list('username',flat=true)  # 不区分大小写

另外，我们可以用 Lower 注释然后将其排序。

User.objects.annotate(
	uname=Lower('username')
).order_by('uname').values_list('username', flat=True)

values_list说明

This is similar to values() except that instead of returning dictionaries, it returns tuples when iterated over. Each tuple contains the value from the respective field passed into the values_list() call — so the first item is the first field, etc. For example:
# 类似于values，但是它不返回字典而是元组，
>>> Entry.objects.values_list('id', 'headline')
[(1, 'First entry'), ...]
If you only pass in a single field, you can also pass in the flat parameter. If True, this will mean the returned results are single values, rather than one-tuples. An example should make the difference clearer:

# 这是传入单个字段
>>> Entry.objects.values_list('id').order_by('id')
[(1,), (2,), (3,), ...]

# 传入单个字段，且flat为True
>>> Entry.objects.values_list('id', flat=True).order_by('id')
[1, 2, 3, ...]
It is an error to pass in flat when there is more than one field.

If you don’t pass any values to values_list(), it will return all the fields in the model, in the order they were declared.

annotate说明

New in Django 1.8:
Previous versions of Django only allowed aggregate functions to be used as annotations. It is now possible to annotate a model with all kinds of expressions.

Each argument to annotate() is an annotation that will be added to each object in the QuerySet that is returned.

The aggregation functions that are provided by Django are described in Aggregation Functions below.

Annotations specified using keyword arguments will use the keyword as the alias for the annotation. Anonymous arguments will have an alias generated for them based upon the name of the aggregate function and the model field that is being aggregated. Only aggregate expressions that reference a single field can be anonymous arguments. Everything else must be a keyword argument.

For example, if you were manipulating a list of blogs, you may want to determine how many entries have been made in each blog:

>>> from django.db.models import Count
>>> q = Blog.objects.annotate(Count('entry'))

# The name of the first blog
>>> q[0].name
'Blogasaurus'

# The number of entries on the first blog
>>> q[0].entry__count
42
The Blog model doesn’t define an entry__count attribute by itself, but by using a keyword argument to specify the aggregate function, you can control the name of the annotation:

>>> q = Blog.objects.annotate(number_of_entries=Count('entry'))
# The number of entries on the first blog, using the name provided
>>> q[0].number_of_entries
42

描述的很清楚了，使用关键字参数指定的注释将使用关键字作为注释的别名，匿名参数将根据聚合函数的名称和正在聚合的模型字段为它们生成一个别名，只有引用单个字段的聚合表达式才能是匿名参数，其他所有内容都必须是关键字参数。

3、如何给两个字段排序

查询集的 order_by 方法可以接收一个或多个属性名，也就允许你可以给两个或多个字段排序。

In [5]: from django.contrib.auth.models import User

In [6]: User.objects.all().order_by("is_active", "-last_login", "first_name")
Out[6]: <QuerySet [<User: Guido>, <User: shabda>, <User: Tim>]>

4、如何给关联模型的一个字段排序

有两个模型类，Category 和 Hero 。

class Category(models.Model):
    name = models.CharField(max_length=100)


class Hero(models.Model):
    # ...
    name = models.CharField(max_length=100)
    category = models.ForeignKey(Category, on_delete=models.CASCADE)

通过类别和英雄名称来给每个类别下的英雄排序

Hero.objects.all().order_by(
    'category__name', 'name'
)
# 注意 'category__name' 中的双下划线。使用双下划线你可以给关联模型的一个字段排序。

查看SQL如下：

SELECT "entities_hero"."id",
       "entities_hero"."name",
       -- more fields
FROM "entities_hero"
INNER JOIN "entities_category" ON ("entities_hero"."category_id" = "entities_category"."id")
ORDER BY "entities_category"."name" ASC,
         "entities_hero"."name" ASC

5、如何给注释字段排序

如果想要获取按 Hero 数量排序的 Category 使用以下的方法：

Category.objects.annotate(
    hero_count=Count("hero")
).order_by(
    "-hero_count"
)
# annotate在上面有详细的介绍