djangodjango-models

Django annotation for using left join


Models:

class Operator_online(models.Model):
    name             =  models.TextField()
    role             =  models.TextField()

class Reservations(models.Model):
    consultant_id     = models.ForeignKey(Operator_online, on_delete=models.CASCADE)
    voip_number       = models.IntegerField()

Tried:

consultants_operators = Operator_online.objects.filter(
    Q(role='consultant') | Q(role='support'), status='1'
)

consultant_id_queryset = Reservations.objects.filter(
    consultant_id__in=consultants_operators
).select_related('consultant_id').annotate(role=F('consultant_id__role')).values(
    'consultant_id',
    'consultant_id__name',
    'voip_number',
    'role'
)

Needs:

Steel returning same above consultant_id_queryset queryset values:

Returning all founded Operator_online (Which is role and name fields) plus existing appropriated Reservations (Which is consultant_id and voip_number) against what before returning only existed Reservations instances appropriated Operator_online.

So for not founded Reservations instance which it's consultant_id field (foreignkey) has related to Operator_online returning null for voip_number and consultant_id


Solution

  • consultants_operators = Operator_online.objects.filter(
        Q(role='consultant') | Q(role='support'), status='1'
    )
    
    reservations_id_subquery = Reservations.objects.filter(
        consultant_id=OuterRef('pk')
    ).values('id')[:1]
    
    reservations_subquery = Reservations.objects.filter(
        consultant_id=OuterRef('pk')
    ).values('voip_number')[:1]
    
    consultant_id_queryset = consultants_operators.annotate(
         reservation_consultant_id=Coalesce(Subquery(reservations_id_subquery), Value(None)),
        reservation_voip_number=Coalesce(Subquery(reservations_subquery), Value(None))
    ).values(
        'role',
        'reservation_voip_number',
        'reservation_consultant_id',
        consultant_id__name=F('name')
    )
    

    It was tried works right, but the issue I was seen that is naming last queryset fields same bellow throws same bellow as response:

    "The annotation 'voip_number' conflicts with a field on the model."

    The issued try

    consultant_id_queryset = consultants_operators.annotate(
        reservation_consultant_id=Coalesce(Subquery(reservations_id_subquery), Value(None)),
        reservation_voip=Coalesce(Subquery(reservations_voip_subquery), Value(None))
    ).values(
        'role',
        consultant_id=F('reservation_consultant_id'),
        voip_number=F('reservation_voip'),
        consultant_id__name=F('name')
    )