django的ORM的表之间关联查询(笔记)

django的ORM关联查询(笔记)

# models.py文件
# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#   * Rearrange models' order
#   * Make sure each model has one field with primary_key=True
#   * Make sure each ForeignKey has `on_delete` set to the desired behavior.
#   * Remove `managed = False` lines if you wish to allow Django to create, modify, and delete the table
# Feel free to rename the models, but don't rename db_table values or field names.
from django.db import models


class Account(models.Model):
    cover = models.CharField(max_length=255)
    nick_name = models.CharField(max_length=255)
    post = models.CharField(max_length=255)
    company = models.CharField(max_length=255)
    phone = models.CharField(max_length=255)
    email = models.CharField(max_length=255)
    ring_email = models.CharField(max_length=255)
    memo = models.CharField(max_length=255)
    psd = models.CharField(max_length=255)
    create_time = models.DateTimeField()
    max_circle = models.IntegerField()
    wechat_id = models.CharField(max_length=255)
    cc = models.ForeignKey('ClassifyCountry', models.DO_NOTHING, db_column='cc')

    class Meta:
        managed = False
        db_table = 'account'


class AccountCircle(models.Model):
    account = models.ForeignKey(Account, models.DO_NOTHING)
    circle = models.ForeignKey('Circle', models.DO_NOTHING)

    class Meta:
        managed = False
        db_table = 'account_circle'

class CountryAttribution(models.Model):
    id = models.IntegerField(primary_key=True)
    title = models.CharField(max_length=255, null=True)

    class Meta:
        managed = False
        db_table = 'country_attribution'


class ClassifyCountry(models.Model):
    id = models.IntegerField(primary_key=True)
    title = models.CharField(max_length=255, null=True)
    attribution = models.ForeignKey(CountryAttribution, on_delete=models.DO_NOTHING, db_column='attribution')

    class Meta:
        managed = False
        db_table = 'classify_country'

1.分组统计出circle_id=1,该圈子里成员所在国家/地区情况
models.AccountCircle.objects.filter(circle_id=1).values("account_id__cc__title").annotate(Count("account_id__cc"))

models.Account.objects.filter(accountcircle__circle_id=1).values("cc__title").annotate(Count("cc"))

结果1：<QuerySet [{'account_id__cc__title': '纽埃', 'account_id__cc__count': 1}, {'account_id__cc__title': '上海市', 'account_id__cc__count': 3}, {'account_id__cc__title': '北京市', 'account_id__cc__count': 1}, {'account_id__cc__title': '伊利诺斯', 'account_id__cc__count': 1}]>

结果2:<QuerySet [{'cc__title': '纽埃', 'cc__count': 1}, {'cc__title': '上海市', 'cc__count': 3}, {'cc__title': '北京市', 'cc__count': 1}, {'cc__title': '伊利诺斯', 'cc__count': 1}]>


2.分组统计出circle_id=1,该圈子里成员的国家/地区所在归属地情况
models.AccountCircle.objects.filter(circle_id=1).values("account_id__cc__attribution__title").annotate(values=Count("*"))

models.Account.objects.filter(accountcircle__circle_id=x).values("cc__attribution__title").annotate(Count("cc__attribution__title"

models.ClassifyCountry.objects.filter(account__accountcircle__circle_id=x).values("attribution__title").annotate(Count("attribution")
结果1：<QuerySet [{'cc__attribution__title': '中国', 'cc__attribution__title__count': 4}, {'cc__attribution__title': '美国', 'cc__attribution__title__count': 1}, {'cc__attribution__title': '其他', 'cc__attribution__title__count': 1}]>
结果2：<QuerySet [{'attribution__title': '中国', 'attribution__count': 4}, {'attribution__title': '美国', 'attribution__count': 1}, {'attribution__title': '其他', 'attribution__count': 1}]>
结果3：<QuerySet [{'account_id__cc__attribution__title': '中国', 'values': 4}, {'account_id__cc__attribution__title': '美国', 'values': 1}, {'account_id__cc__attribution__title': '其他', 'values': 1}]>