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

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')
)
Back to Top