# Finding items NOT in a table.

**URL:** <https://forum.djangoproject.com/t/finding-items-not-in-a-table/36068>\
**Category:** Using the ORM\
**Created:** [October 29, 2024, 10:18pm UTC](https://forum.djangoproject.com/t/finding-items-not-in-a-table/36068 "2024-10-29T22:18:47Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Yzupnick](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/yzupnick/32/24876_2.png) [@Yzupnick](https://forum.djangoproject.com/u/Yzupnick)\
**Post date:** [October 29, 2024, 10:18pm UTC](https://forum.djangoproject.com/t/finding-items-not-in-a-table/36068/1 "2024-10-29T22:18:47Z")

</div>

I have a list of URLs in memory, I have a table of URLs that I have visited.  
Is there a way to use the Django ORM to find which URLs in memory are not in the table using a single query?

In SQL I would use some sort of Temptable or equivalent to join against the model table.  
Any good options with the ORM?  
Thanks!

---

<div class="post-metadata">

**Author:** ![boxed](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/boxed/32/11975_2.png) [@boxed](https://forum.djangoproject.com/u/boxed)\
**Post date:** [October 29, 2024, 11:58pm UTC](https://forum.djangoproject.com/t/finding-items-not-in-a-table/36068/2 "2024-10-29T23:58:48Z")

</div>

```py
Foo.objects.filter(exclude __url__ in=urls)

```

maybe?

---

<div class="post-metadata">

**Author:** ![white-seolpyo](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/white-seolpyo/32/22386_2.png) [@white-seolpyo](https://forum.djangoproject.com/u/white-seolpyo)\
**Post date:** [October 30, 2024, 12:10am UTC](https://forum.djangoproject.com/t/finding-items-not-in-a-table/36068/3 "2024-10-30T00:10:07Z")

</div>

```auto
model.objects.exclude(url__in={url list})

```

---

<div class="post-metadata">

**Author:** ![Yzupnick](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/yzupnick/32/24876_2.png) [@Yzupnick](https://forum.djangoproject.com/u/Yzupnick)\
**Post date:** [October 30, 2024, 12:17am UTC](https://forum.djangoproject.com/t/finding-items-not-in-a-table/36068/4 "2024-10-30T00:17:54Z")

</div>

Thanks!

I think these return records that are in the table that aren’t in the list.  
I want items in the list that aren’t in the table.

---

<div class="post-metadata">

**Author:** ![onyeibo](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/onyeibo/32/11494_2.png) [@onyeibo](https://forum.djangoproject.com/u/onyeibo)\
**Post date:** [October 30, 2024, 4:38am UTC](https://forum.djangoproject.com/t/finding-items-not-in-a-table/36068/5 "2024-10-30T04:38:18Z")

</div>

Assuming the list in memory is `mem_list` and the field holding the **visited urls** in the `Urls` table is `visited` .

```auto
# grab the urls in the table that are in the list
qs = Urls.objects.filter(visited__in=mem_list)

```

```auto
# flatten the resulting queryset from above into a list and use set() to extract the difference
set(mem_list).difference(set(qs.values_list("visited", flat=True)))

```

You can turn all the above into a one-liner:  
`set(mem_list).difference(set(Urls.objects.filter(visited__in=mem_list).values_list("visited", flat=True)))`

---

<div class="post-metadata">

**Author:** ![CodenameTim](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/codenametim/32/31507_2.png) [@CodenameTim](https://forum.djangoproject.com/u/CodenameTim)\
**Post date:** [October 30, 2024, 9:46am UTC](https://forum.djangoproject.com/t/finding-items-not-in-a-table/36068/6 "2024-10-30T09:46:07Z")

</div>

Have you tried using a FilteredRelation where it is null? It will do a left join allowing null values. Filtering on null values will help you identify where relationships don’t exist efficiently.

> **[QuerySet API reference | Django documentation](https://docs.djangoproject.com/en/5.1/ref/models/querysets/#filteredrelation-objects)**
>
> The web framework for perfectionists with deadlines.

---

<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:** [October 30, 2024, 12:51pm UTC](https://forum.djangoproject.com/t/finding-items-not-in-a-table/36068/7 "2024-10-30T12:51:43Z")

</div>

Using a set difference against a `__in=urls` lookup as suggested by @onyeibo is definitely the best way to do it.