Django ORM query join unrelated tables on two fields

Question

The problem

I'm writing a program where I register treatments performed on patients in various medical places. Each treatment is performed on certain body part and each medical place can define their own way of naming those body parts.

My models look like this:

class Treatment(Model):
    medical_place = ForeignKey(to=MedicalPlace, on_delete=PROTECT, related_name='treatments')
    body_part = CharField(max_length=64, choices=BodyPart.choices(), db_index=True)
    ... (some other fields) ...

class LocalProcedure(ProcedureBase):
    medical_place = ForeignKey(to=MedicalPlace, on_delete=PROTECT, related_name='procedures')
    identifier = CharField(verbose_name=_('Procedure number'), max_length=64, blank=False, null=False)
    description = CharField(verbose_name=_('Procedure description'), max_length=256, blank=True, null=False)
    body_part = CharField(max_length=64, choices=BodyPart.choices(), blank=False, db_index=True)

    class Meta:
        unique_together = (('body_part', 'medical_place', 'identifier'),)

I need to retrieve list of "LocalProcedure" objects with annotated:

count of related treatments
count of related treatments performed up to X days ago (Treatments model has created datetime field I didn't show here)

Solutions I have tried so far

I can retrieve annotated list of LocalProcedures while using subqueries:

    related_treatments = Treatment.objects.filter(
        body_part=OuterRef('body_part'),
        medical_places=OuterRef('medical_place'),
    ).values(
        f'body_part',
    )
    LocalProcedure.objects.annotate(
        treatments_count = Subquery(related_treatments.annotate(count=Count('pk')).values('count'),
        treatments_count = Subquery(related_treatments.filter(created__gt=now()).annotate(count=Count('pk')).values('count'),
    )

but this solution does indeed perform subqueries, whereas joining those two tables (when writing raw query) is much much faster.

Any help appreciated.

Partially working solution (in the end marked as accepted)

The answer posted here by @ruddra has been really helpful, but does not meet my need completely. I know that I have relation between LocalProcedure and Treatment object via medical_place field but this means that the query would have another, completely unnecessary join clause...

As for people looking for a way to introduce relations to their models without ForeignKey - there is something called ForeignObject and can be used to create relations between two objects using any fields.

score 2 · Accepted Answer · answered Apr 03 '20 at 07:41

2

Probably this would be much simpler:

from django.db.models import Count, F, Q

LocalProcedure.objects.annotate(
    treatments_count=Count(
        'medical_places__treatments',
        filter=Q(body_part=F('body_part'))
    ),
    treatments_count_x=Count(
        'medical_places__treatments',
        filter=Q(body_part=F('body_part'))|Q(created_gt=now())
    )
)

Here I am just counting with a filter based on conditional expression.

answered Apr 03 '20 at 07:41

ruddra

50,746
7
78
101

Thanks, I will try it shortly. As I can see you are using `medical_place` table as a mutual point for those two tables. What if I wanted to omit it in LocalProcedure class? Would you still be able to provide me with a solution like this? I really appreciate you help. – Waszker Apr 03 '20 at 07:52
I do not think it will work without `medical_place`, I mean if there is no relationship, then Count won't work. – ruddra Apr 03 '20 at 08:20

Django ORM query join unrelated tables on two fields

The problem

Solutions I have tried so far

Partially working solution (in the end marked as accepted)

1 Answers1