# recreating IS NOT DISTINCT FROM in django ORM

**URL:** <https://forum.djangoproject.com/t/recreating-is-not-distinct-from-in-django-orm/39249>\
**Category:** Using the ORM\
**Created:** [March 2, 2025, 1:37pm UTC](https://forum.djangoproject.com/t/recreating-is-not-distinct-from-in-django-orm/39249 "2025-03-02T13:37:55Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Malvi1697](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/malvi1697/32/27231_2.png) [@Malvi1697](https://forum.djangoproject.com/u/Malvi1697)\
**Post date:** [March 2, 2025, 1:37pm UTC](https://forum.djangoproject.com/t/recreating-is-not-distinct-from-in-django-orm/39249/1 "2025-03-02T13:37:55Z")

</div>

Hello everyone, I’m trying to recreate this sql using Django ORM, how would I go about it?

```sql
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

```python
Food.objects.annotate(sweet_id=fruit __sweets__ id)

```

to recreate

```sql
SELECT food.*
FROM food
         LEFT JOIN sweet
                   ON (sweet.fruit_id = food.fruit_id)

```

But I have a hard time recreating the

```sql
AND (sweet.category_id IS NOT DISTINCT FROM food.category_id);

```

on it’s own or even combined

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [March 2, 2025, 2:40pm UTC](https://forum.djangoproject.com/t/recreating-is-not-distinct-from-in-django-orm/39249/2 "2025-03-02T14:40:53Z")

</div>

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.

- Write a [custom lookup](https://docs.djangoproject.com/en/5.1/howto/custom-lookups/#how-to-write-custom-lookups)

- Use [the `extra` clause](https://docs.djangoproject.com/en/5.1/ref/models/querysets/#extra)

- Use [raw sql](https://docs.djangoproject.com/en/5.1/topics/db/sql/#performing-raw-sql-queries)

- Build the equivalent expression as a combination of other clauses.

Side note:

> [@Malvi1697](#):
>
> `Food.objects.annotate(sweet_id=fruit __sweets__ id)`

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.

---

<div class="post-metadata">

**Author:** ![Malvi1697](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/malvi1697/32/27231_2.png) [@Malvi1697](https://forum.djangoproject.com/u/Malvi1697)\
**Post date:** [March 2, 2025, 2:53pm UTC](https://forum.djangoproject.com/t/recreating-is-not-distinct-from-in-django-orm/39249/3 "2025-03-02T14:53:31Z")

</div>

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:​

```python
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)
)
)

```

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [March 2, 2025, 2:58pm UTC](https://forum.djangoproject.com/t/recreating-is-not-distinct-from-in-django-orm/39249/4 "2025-03-02T14:58:36Z")

</div>

Again, what are you doing with the results of this query?

Depending upon usage, you may not need these:

> [@Malvi1697](#):
>
> ```auto
> select_related('fruit').prefetch_related(
> 'fruit__sweets'
> )
> 
> ```

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.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [March 4, 2025, 9:21pm UTC](https://forum.djangoproject.com/t/recreating-is-not-distinct-from-in-django-orm/39249/5 "2025-03-04T21:21:04Z")

</div>

> 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](https://docs.djangoproject.com/en/5.1/howto/custom-lookups/#how-to-write-custom-lookups) as @KenWhitesell pointed out

```python
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](https://docs.djangoproject.com/en/5.1/ref/models/querysets/#filteredrelation-objects)

```python
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.

---

<div class="post-metadata">

**Author:** ![Malvi1697](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/malvi1697/32/27231_2.png) [@Malvi1697](https://forum.djangoproject.com/u/Malvi1697)\
**Post date:** [March 5, 2025, 11:21am UTC](https://forum.djangoproject.com/t/recreating-is-not-distinct-from-in-django-orm/39249/6 "2025-03-05T11:21:46Z")

</div>

Thanks for the great solution and example! However, I ran into a strange issue when using only `FilteredRelation`. Here’s my code:

```python
# 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`:

```bash
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:

```sql
         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:

```sql
         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.

---

<div class="post-metadata">

**Author:** ![Malvi1697](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/malvi1697/32/27231_2.png) [@Malvi1697](https://forum.djangoproject.com/u/Malvi1697)\
**Post date:** [March 5, 2025, 11:31am UTC](https://forum.djangoproject.com/t/recreating-is-not-distinct-from-in-django-orm/39249/7 "2025-03-05T11:31:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [March 5, 2025, 1:59pm UTC](https://forum.djangoproject.com/t/recreating-is-not-distinct-from-in-django-orm/39249/8 "2025-03-05T13:59:16Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Malvi1697](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/malvi1697/32/27231_2.png) [@Malvi1697](https://forum.djangoproject.com/u/Malvi1697)\
**Post date:** [March 5, 2025, 2:17pm UTC](https://forum.djangoproject.com/t/recreating-is-not-distinct-from-in-django-orm/39249/9 "2025-03-05T14:17:31Z")

</div>

Django version: 5.0.11

```python
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,
    )
...

```

```python
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
    )
...

```

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [March 5, 2025, 3:16pm UTC](https://forum.djangoproject.com/t/recreating-is-not-distinct-from-in-django-orm/39249/10 "2025-03-05T15:16:49Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Malvi1697](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/malvi1697/32/27231_2.png) [@Malvi1697](https://forum.djangoproject.com/u/Malvi1697)\
**Post date:** [March 5, 2025, 5:21pm UTC](https://forum.djangoproject.com/t/recreating-is-not-distinct-from-in-django-orm/39249/11 "2025-03-05T17:21:40Z")

</div>

Okay, I will try it tomorrow and report back soon. Thank you!