Hello everyone, I’m trying to recreate this sql using Django ORM, how would I go about it?
SELECT food.*
FROM food
LEFT JOIN sweet
ON (sweet.fruit_id = food.fruit_id) AND
(sweet.category_id IS NOT DISTINCT FROM food.category_id);
so far I found out I can do
Food.objects.annotate(sweet_id=fruit__sweets__id)
to recreate
SELECT food.*
FROM food
LEFT JOIN sweet
ON (sweet.fruit_id = food.fruit_id)
But I have a hard time recreating the
AND (sweet.category_id IS NOT DISTINCT FROM food.category_id);
on it’s own or even combined
Welcome @Malvi1697 !
Django itself does not directly support the ‘is distinct from’ SQL operator.
You’ve got a couple of options for handling this yourself.
Side note:
This may not be necessary depending upon the full context of what you’re trying to do here with this data after it has been queried. Django handles a lot of these joins implicitly - there’s rarely a need to try and “force” a join to occur.
You need to look at this as a whole - not just the immediate query you’re trying to execute, but what you’re going to do with the results of that query within the view.
2 Likes
Thank you, Ken. I’ve continued investigating and developed this query. While it doesn’t exactly correspond to these SQL joins, the results are nearly identical, with only five rows missing out of 90,000. I’ve done this:
Food.objects.select_related('fruit').prefetch_related(
'fruit__sweets'
).filter(
Q(
fruit__sweets__category=F('category')
) |
Q(
fruit__sweets__category__isnull=True,
category__isnull=True
) |
Q(fruit__sweets__isnull=True)
)
)
Again, what are you doing with the results of this query?
Depending upon usage, you may not need these:
These clauses are not required for generating references to related data within the query. They are only a performance enhancement when accessing this related data from the objects that have been retrieved.
2 Likes
While it doesn’t exactly correspond to these SQL joins, the results are nearly identical
If you want to achieve the exact same SQL you should write a custom lookup as @KenWhitesell pointed out
from django.db.models import Field, Lookup
@Field.register_lookup
class DistinctFrom(Lookup):
lookup_name = "distinctfrom"
def as_sql(self, compiler, connection):
lhs, lhs_params = self.process_lhs(compiler, connection)
rhs, rhs_params = self.process_rhs(compiler, connection)
params = lhs_params + rhs_params
return "%s IS DISTINCT FROM %s" % (lhs, rhs), params
and use a FilteredRelation annotation
Fruit.objects.annotate(
filtered_fruits_sweets=FilteredRelation(
"fruits__sweets",
condition=~Q(fruits__sweets__category__distinctfrom=F("category"))
)
)
Note that you’ll likely have to explicitly reference filtered_fruits_sweets for the filtering to be applied as the Django ORM is not a query builder per-se but a way to retrieve data.
With that in mind it’s much better to approach these kind problem in terms of what exactly you are trying to do with the data, and communicate it in your requests for support, than the other way around as Ken pointed out.
1 Like
Thanks for the great solution and example! However, I ran into a strange issue when using only FilteredRelation. Here’s my code:
# this changes dynamically
event_type_id = "60ae604c-90e1-495b-8a0e-512f29676e39"
result = (
Plan.objects.annotate(
filtered_eventplan=FilteredRelation(
"part__event_plans",
condition=Q(
part__event_plans__event_type_id=event_type_id,
part__event_plans__part_id=F("part_id"),
)
& Q(
Q(part__event_plans__circuit_id=F("circuit_id"))
| (
Q(part__event_plans__circuit_id__isnull=True)
& Q(circuit_id__isnull=True)
)
),
)
)
.filter(
Q(foreign=False)
& (Q(cancellation_date__isnull=True) | Q(cancellation_date__gt=now.date()))
)
.annotate(identifier=F("filtered_eventplan__event_type__identifier"))
)
Whenever I try to annotate a field that references a foreign key used in the FilteredRelation condition, I get a ProgrammingError:
django.db.utils.ProgrammingError: missing FROM-clause entry for table "filtered_eventplan"
LINE 1: ...") LEFT OUTER JOIN "administration_eventtype" ON (filtered_e...
For example, since I use part__event_plans__part and part__event_plans__event_type (both foreign keys) in the FilteredRelation I cannot access them in my next .annotate(). The raw SQL join generated looks likes this:
LEFT OUTER JOIN "department_part" ON ("department_plan"."part_id" = "department_part"."id")
LEFT OUTER JOIN "administration_eventtype"
ON (filtered_eventplan."event_type_id" = "administration_eventtype"."id")
LEFT OUTER JOIN "department_partcircuit"
ON ("department_plan"."circuit_id" = "department_partcircuit"."id")
LEFT OUTER JOIN "department_eventplan" filtered_eventplan
ON ("department_part"."id" = filtered_eventplan."part_id" AND
((filtered_eventplan."event_type_id" =
'60ae604c-90e1-495b-8a0e-512f29676e39' AND
filtered_eventplan."part_id" = ("department_plan"."part_id") AND
(filtered_eventplan."circuit_id" =
("department_plan"."circuit_id") OR
(filtered_eventplan."circuit_id" IS NULL AND
"department_plan"."circuit_id" IS NULL)))))
WHERE (NOT "department_plan"."foreign" AND ("department_plan"."cancellation_date" IS NULL OR
"department_plan"."cancellation_date" >
'2025-03-05 00:00:00+01:00'))
In the SQL output, you can see this join:
LEFT OUTER JOIN "administration_eventtype"
ON (filtered_eventplan."event_type_id" = "administration_eventtype"."id")
LEFT OUTER JOIN "department_partcircuit"
ON ("department_plan"."circuit_id" = "department_partcircuit"."id")
LEFT OUTER JOIN "department_eventplan" filtered_eventplan
ON ("department_part"."id" = filtered_eventplan."part_id" AND
This means Django is trying to reference filtered_eventplan before it exists in the query, causing the ProgrammingError
I’m a junior developer and have been programming for six months. This is my first project, so sorry if I overexplained or underexplained anything—I’m still learning how to structure technical questions effectively.
I am not sure if you’ll get a ping since I didn’t hit reply, therefore just to be sure I’m replying here.
It’s possible that you are either running into a FilteredRelation bug or running an older version of Django.
Could you provide the version of Django your are using and your model definition?
Django version: 5.0.11
class EventPlan(ProjectModel):
objects = EventPlanManager()
part = models.ForeignKey(
"department.Part", on_delete=models.CASCADE, related_name="event_plans"
)
circuit = models.ForeignKey(
"department.PartCircuit",
models.DO_NOTHING,
related_name="event_plans",
blank=True,
null=True,
)
event_type = models.ForeignKey(
"administration.EventType",
models.DO_NOTHING,
related_name="event_plans",
null=True,
blank=True,
)
...
class Plan(ProjectModel):
objects = PlanManager()
device = models.ForeignKey(
"Device", on_delete=models.CASCADE, related_name="plans", blank=True, null=True
)
part = models.ForeignKey(
"Part", on_delete=models.CASCADE, related_name="plans", blank=True, null=True
)
circuit = models.ForeignKey(
"PartCircuit", models.DO_NOTHING, related_name="plans", blank=True, null=True
)
...
The first thing I’d try is upgrading to Django 5.1 (or even 5.2b1) as it contains a few FilteredRelation bugfixes. It that doesn’t cut it there might another bug lurking here that needs further diagnosis.
Okay, I will try it tomorrow and report back soon. Thank you!