django:联表查询多字段

1. 联表查询

1.联表并取两表字段

from django.db.models import Exists, OuterRef

# 1.已验证
DataTickets.objects.filter(
    create_time__gte=start_time,
    create_time__lte=end_time,
).values('requestor_id',
 	xx=BizUserGroupResult.objects.filter(customer_id=OuterRef('requestor_id')).values('xx'))  # 必须为filter，get报错，filter包含0或1条，多条报错

# 2.待验证
DataTickets.objects.filter(
	create_time__time__gte=start_time,
	create_time__time__lte=end_time).extra(
	    select={
            'is_rest': f"select is_rest from {BizBsFeature._meta.db_table} where is_rest=Date({DataTickets._meta.db_table}.create_time)"  # 关联
        }
    ).values('create_time__date', 'is_rest')
    
# 3.已验证
aa = DataCustomer.objects.filter(customer_id__in=similar_users,cell_phone=OuterRef('cell_number')
DataPhone.objects.filter(Exists(aa))).annotate(call_id_=Subquery(aa).values('customer_id')))  # call_id_子表中的列

2.联表只取主表字段

from django.db.models import Exists, OuterRef


DataTickets.objects.filter(
    Exists(
        BizUserGroupResult.objects.filter(
            group_base_id=instance.group_base_id,
            customer_id=OuterRef('requestor_id'))
        ),
    create_time__gte=start_time,
    create_time__lte=end_time,
).values_list('requestor_id', flat=True)

2. select_related与prefetch_related

from django.db.models import OuterRef, Subquery, F, Count, Prefetch, Exists
from seqauto_cn.models import SequencingProject, Aggregation, Analysis
from seqauto_cn.models.processing_environment import get_bu_environment
from seqauto.models import SequencedSample, Sample

# 所有Aggregation中相同project_id的取日期最新
success_aggregation = Aggregation.objects.filter(
    related_analysis__analysis_params__project__sequencing_project__id=OuterRef('pk'), state='FINISHED')
# 日期最新的Aggregation中state==FINISHED的所有SequencingProject并连带出sample，sequencing_analysis_params表数据
allowed_sequencing_project = SequencingProject.objects.annotate(
    has_success_aggregation=Exists(success_aggregation)
).filter(has_success_aggregation=True).select_related(
    'user',  # 外键
    'analysis_params__sequencing_analysis_params',  # 1对1__1对1
).prefetch_related(
    'analysis_params__analysis_set',  # 1对1__1对多
    'sample_set',  # 1对多
    Prefetch(
        'sample_set',
        queryset=Sample.objects.select_related('sequenced_sample'),
        to_attr='prefetched_sample'
    ),  # 1对多__1对1
    Prefetch(
        'analysis_params__analysis_set__aggregation_set',
        queryset=Aggregation.objects.all(),
        to_attr='prefetched_aggregation'
    )  # 1对1__1对多_1对多
)

# 用法
for project in allowed_sequencing_project:
    project.user.username
    project.analysis_params.sequencing_analysis_params

    for i in project.analysis_params.analysis_set.all():
        pass
    for i in project.sample_set.all():
        pass
    project.prefetched_sample
    for i in project.prefetched_aggregation.all():
        pass