Django model high find(Django模型高级查询）

Django model high find

Model

class Employees(models.Model):
    emp_no = models.IntegerField(primary_key=True)
    birth_date = models.DateField()
    first_name = models.CharField(max_length=14)
    last_name = models.CharField(max_length=16)
    gender = models.CharField(max_length=1)
    hire_date = models.DateField()

    class Meta:
        managed = False
        db_table = 'employees'

View

group_by

**importance ** if you want to group in Django, You must use annotate with order_by and values。

重要，如果你想在Django进行分组，你必须annotate和order_by同时使用。

Employees.objects.values('birth_date').annotate(count_birth=Count('birth_date')).order_by('birth_date').all()

generate sql

SELECT `employees`.`birth_date`, COUNT(`employees`.`birth_date`) AS `count_birth` FROM `employees` GROUP BY `employees`.`birth_date` ORDER BY `employees`.`birth_date` ASC

order_by

if you want to order_by in django. You can use order_by.

 employs = Employees.objects.order_by('birth_date').all()

generate sql

SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date` FROM `employees` ORDER BY `employees`.`birth_date` ASC

indicates to query certain fields(只查询某些字段)

if you query all the fields。this is a time-consuming operation. （如果你想查询所有的字段，这是一个耗时的操作）

so we need fields queries. 所以我们需要指定字段查询

values

employs = Employees.objects.values('birth_date').all()

generate sql

 SELECT `employees`.`birth_date` FROM `employees`

use mysql fun

if we want to use Count, Max, or Sum and some other func。we can importing Django‘s build-in methods.

如果你想使用count，Max或者sum等其他一些函数，我们可以导入Django的一些内置方法。

count

from django.db.models import Max, Count
Employees.objects.values('birth_date').annotate(count_birth=Count('birth_date')).order_by('birth_date').all()

Generate sql

select `employees`.`birth_date`, COUNT(`employees`.`birth_date`) AS `count_birth` FROM `employees` GROUP BY `employees`.`birth_date` ORDER BY `employees`.`birth_date` ASC

use aggregate func

Django provides two aggregate function。annotate and aggregate。

annotate

Annotate allow you to alias fields and use aggreate func

example

Employees.objects.values('emp_no', ).annotate(name=F('emp_no')).all()

generate_sql

SELECT `employees`.`emp_no`, `employees`.`birth_date`, `employees`.`first_name`, `employees`.`last_name`, `employees`.`gender`, `employees`.`hire_date`, `employees`.`emp_no` AS `name` FROM `employees` LIMIT 10'

aggregate

**aggregate return a dict not a queryset ** aggregate also always the fields to to aliasd

aggratale 返回的是一个字典，并不是一个queryset。他也支持给字段取别名

Employees.objects.aaggregate(Max('birth_date'))

return result

{'birth_date__max': datetime.date(1965, 2, 1)}

complex query（复杂查找）

here’s some data。I wish to cut useing - sample, and then sort the front fields and the last fields.

这有一些数据，我希望使用-对sample进行切割，然后使用前面的字段和后面的字段进行排序。

example

        sample_detail = SampleDetail.objects.values('sample_no', ).annotate(
            first_field=Left('sample_no', StrIndex('sample_no', Value('-')) - 1),
            last_field=Right('sample_no', StrIndex('sample_no', Value('-')) - 1)
        ).order_by('first_field', 'last_field').all()

generage_sql

SELECT `sample_detail`.`sample_no`, LEFT(`sample_detail`.`sample_no`, (INSTR(`sample_detail`.`sample_no`, -) - 1)) AS `first_field`, RIGHT(`sample_detail`.`sample_no`, (INSTR(`sample_detail`.`sample_no`, -) - 1)) AS `last_field` FROM `sample_detail` ORDER BY `first_field` ASC, `last_field` ASC