# using union() with querysets that have a custom Prefetch() object

**URL:** <https://forum.djangoproject.com/t/using-union-with-querysets-that-have-a-custom-prefetch-object/41638>\
**Category:** Using the ORM\
**Created:** [July 1, 2025, 10:00am UTC](https://forum.djangoproject.com/t/using-union-with-querysets-that-have-a-custom-prefetch-object/41638 "2025-07-01T10:00:18Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![gerien](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/gerien/32/20691_2.png) [@gerien](https://forum.djangoproject.com/u/gerien)\
**Post date:** [July 1, 2025, 10:00am UTC](https://forum.djangoproject.com/t/using-union-with-querysets-that-have-a-custom-prefetch-object/41638/1 "2025-07-01T10:00:18Z")

</div>

Hi,

I’m trying to do something like this (simplified for the example):

```python
n1 = Network.objects.filter(code="net1").prefetch_related(Prefetch("station_set", queryset=Station.objects.filter(code="sta1")))
n2 = Network.objects.filter(code="net2").prefetch_related(Prefetch("station_set", queryset=Station.objects.filter(code="sta2")))
networks = n1.union(n2)

```

It partially works. But the problem is the following :

```python
networks[0].station_set.all()

```

returns `<QuerySet [<Station: sta1>]>` as expected

but

```python
networks[1].station_set.all()

```

returns `<QuerySet []>` (I would have expected it to return `<QuerySet [<Station: sta2>]>`)

It seems that union() method doesn’t include custom prefetches. Regular prefetches (`Network.objects.filter(code="net2").prefetch_related("station_set")`) seems to be returned correctly.  
Is it an expected behavior or not ?

To solve this issue, I tried :

- using `n1 | n2` but the query takes more than 15 seconds to process
- using list(chain(n1, n2)) which works but doesn’t distinct results when necessary

Does anyone encountered the same problem ? I’m wondering how to solve my issue.

Thanks in advance

---

<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:** [July 1, 2025, 1:49pm UTC](https://forum.djangoproject.com/t/using-union-with-querysets-that-have-a-custom-prefetch-object/41638/2 "2025-07-01T13:49:02Z")

</div>

Side note: Do you have a typo here or two?

You have:

> [@gerien](#):
>
> `n2 = Network.objects.filter(code="net2").prefetch_related(Prefetch("station_set", queryset=Station.objects.filter(code="sta25")))`

but then you wrote:

> [@gerien](#):
>
> returns `<QuerySet []>` (I would have expected it to return `<QuerySet [<Station: sta1>]>`)

Why would you expect it to return that value with the filter you have defined? Is `networks[0]` a reference to the same object as `networks[1]`?

Without seeing the model definitions, and a sufficient representation of the data in those models to demonstrate this issue, and possibly even the queries being generated, it’s going to be pretty much impossible for us to provide any kind of diagnostic assistance.

---

<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:** [July 1, 2025, 2:09pm UTC](https://forum.djangoproject.com/t/using-union-with-querysets-that-have-a-custom-prefetch-object/41638/3 "2025-07-01T14:09:27Z")

</div>

> [@gerien](#):
>
> Regular prefetches (`Network.objects.filter(code="net2").prefetch_related("station_set")`) seems to be returned correctly.  
> Is it an expected behavior or not ?

`prefetch_related` is applied on the resulting set once the union of results has been retrieved so if you need to prefetch a set of result that depends on the outcome of the union you must craft a queryset accordingly.

Something like the following should do (I’m making assumptions here as you didn’t provide your model definitions)

```python
Network.objects.filter(code="net1").union(
    Network.objects.filter(code="net2")
).prefetch_related(
    Prefetch(
        "station_set",
        queryset=Station.objects.filter(
            Q(network__code="net1", code="stat1")
            | Q(network__code="net2", code="stat25")
        )
    )
)

```

If the ORM supported further filtering union of querysets it could potentially automatically do some of the above [but it’s not the case yet](https://code.djangoproject.com/ticket/28519).

---

<div class="post-metadata">

**Author:** ![gerien](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/gerien/32/20691_2.png) [@gerien](https://forum.djangoproject.com/u/gerien)\
**Post date:** [July 1, 2025, 2:13pm UTC](https://forum.djangoproject.com/t/using-union-with-querysets-that-have-a-custom-prefetch-object/41638/4 "2025-07-01T14:13:08Z")

</div>

> [@KenWhitesell](#):
>
> expect it to return that value with the filter you have defined? Is `networks[0]` a reference to the same object as `networks[1]`?

Hi Ken,  
Sorry for that. I fixed the typo in the first post

> [@KenWhitesell](#):
>
> Without seeing the model definitions, and a sufficient representation of the data in those models to demonstrate this issue, and possibly even the queries being generated, it’s going to be pretty much impossible for us to

A simplified models.py would look like :

```python
class Network(models.Model):
    code = models.CharField(max_length=8)

class Station(models.Model):
    code = models.CharField(max_length=8)
    network = models.ForeignKey(Network, on_delete=models.PROTECT)

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=["code", "network"], name="station_code_network_id_unique"
            )
        ]

```

So in my example I have 2 networks (net1 and net2) and 2 stations (sta1 related to net1 and sta2 related to net2)

---

<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:** [July 1, 2025, 2:18pm UTC](https://forum.djangoproject.com/t/using-union-with-querysets-that-have-a-custom-prefetch-object/41638/5 "2025-07-01T14:18:38Z")

</div>

Well it looks like calling `prefetch_related` after `union` is disallowed which I’m unsure why but if you bundle it up in your first queryset everything should work as expected.

Have a look [here](https://dryorm.xterm.info/union-prefetch-v2).

It looks like we should lift the restriction on using `prefetch_related` after `union` as long as the queryset is dealing with model instances (as long as no `values` / `values_list` mask has been used).

---

<div class="post-metadata">

**Author:** ![gerien](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/gerien/32/20691_2.png) [@gerien](https://forum.djangoproject.com/u/gerien)\
**Post date:** [July 1, 2025, 2:52pm UTC](https://forum.djangoproject.com/t/using-union-with-querysets-that-have-a-custom-prefetch-object/41638/6 "2025-07-01T14:52:25Z")

</div>

I’m trying to to something a bit different :  
responding one shot to a request that would ask  
"Give me all networks and their related stations that follow the following constraints :

- station\_code = sta25 related to network\_code = net2
- station\_code = sta1 related to network\_code = net1
- etc…"

So, with the following corrected code, ([Dry ORM](https://dryorm.xterm.info/netstagerien))

```python
from django.db import models
from django.db.models import Prefetch, Q

class Network(models.Model):
    code = models.CharField(max_length=8)
    
    def __str__ (self) -> str:
        return self.code

class Station(models.Model):
    code = models.CharField(max_length=8)
    network = models.ForeignKey(Network, on_delete=models.PROTECT)
    
    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=["code", "network"], name="station_code_network_id_unique"
            )
        ]

    def __str__ (self) -> str:
        return self.code
    
def run():
    net1 = Network.objects.create(code="net1")
    net2 = Network.objects.create(code="net2")
    stat1 = Station.objects.create(
        network=net1, code="stat1",
    )
    stat25 = Station.objects.create(
        network=net2, code="stat25",
    )
    networks = list(
        Network.objects.filter(code="net1").prefetch_related(
            Prefetch(
                "station_set",
                queryset=Station.objects.filter(code="stat1")
            )
        ).union(
            Network.objects.filter(code="net2").prefetch_related(
                Prefetch(
                    "station_set",
                    queryset=Station.objects.filter(code="stat25")
                )
            )
        )
    )
    print("with custom prefetch")
    print(networks[0].station_set.all())
    print(networks[1].station_set.all())
    networks = list(
        Network.objects.filter(code="net1").prefetch_related("station_set").union(
        Network.objects.filter(code="net2").prefetch_related("station_set")
    ))
    print("with regular prefetch")
    print(networks[0].station_set.all())
    print(networks[1].station_set.all())

```

I get the following result :

```python
with custom prefetch
<QuerySet [<Station: stat1>]>
<QuerySet []>
with regular prefetch
<QuerySet [<Station: stat1>]>
<QuerySet [<Station: stat25>]>

```

I would have expected both results to be the same here…

In my example, `union` is called after `prefetch_related` (no exception is thrown)

PS : thanks for the ultra quick replies !

---

<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:** [July 1, 2025, 3:50pm UTC](https://forum.djangoproject.com/t/using-union-with-querysets-that-have-a-custom-prefetch-object/41638/7 "2025-07-01T15:50:16Z")

</div>

> I would have expected both results to be the same here…

It won’t as the `prefetch_related` call of the queryset passed to union is entirely ignored.

In other words, these three are equivalent

```python
one = Network.objects.filter(code="net1").prefetch_related(
    Prefetch(
        "station_set",
        queryset=Station.objects.filter(code="stat1")
    )
).union(
    Network.objects.filter(code="net2").prefetch_related(
        # XXX: This is all silently ignored.
        Prefetch(
            "station_set",
            queryset=Station.objects.filter(code="stat25")
        )
    )
)
#####
two = Network.objects.filter(code="net1").prefetch_related(
    Prefetch(
        "station_set",
        queryset=Station.objects.filter(code="stat1")
    )
).union(
    Network.objects.filter(code="net2")
)
#####
# Assuming `prefetch_related` was allowed after `union`
three = Network.objects.filter(code="net1").union(
    Network.objects.filter(code="net2")
).prefetch_related(
    Prefetch(
        "station_set",
        queryset=Station.objects.filter(code="stat1")
    )
)

```

You should take a look at [the generated SQL tab](https://dryorm.xterm.info/union-prefetch-v2) and you’ll notice that the only way to achieve what you’re after is to pass a custom `prefetch_related` to the queryset prior to calling `union`. You’re only getting the _right_ results in your _regular_ prefetch example because you don’t have any station that would normally be filtered out.

---

<div class="post-metadata">

**Author:** ![gerien](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/gerien/32/20691_2.png) [@gerien](https://forum.djangoproject.com/u/gerien)\
**Post date:** [July 2, 2025, 12:50pm UTC](https://forum.djangoproject.com/t/using-union-with-querysets-that-have-a-custom-prefetch-object/41638/8 "2025-07-02T12:50:23Z")

</div>

Ok, I get it now.

It will be difficult for me to use your solution as in my real use case, I have several levels of prefetched objects and as many unions as requested.  
But for now, I can live with the list(chain(querysets)) option.

Do you think opening an issue on django bug tracker to support `prefetch_related()` querysets would be a good idea ?  
Anyway, thanks for the answers !

---

<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:** [July 2, 2025, 6:41pm UTC](https://forum.djangoproject.com/t/using-union-with-querysets-that-have-a-custom-prefetch-object/41638/9 "2025-07-02T18:41:07Z")

</div>

> Do you think opening an issue on django bug tracker to support `prefetch_related()` querysets would be a good idea ?

Allowing `prefetch_related` to be called after `union` (as long as `values` and `values_list` are not used) should be acceptable (we demonstrated here that the check can be bypassed anyway) but implementing an automatic merge logic for union of prefetches is unlikely to be given the complexity involved.

Code wise this means allowing

```python
Network.objects.filter(code="net1").union(
    Network.objects.filter(code="net2")
).prefetch_related(...)

```

is likely a good candidate but getting

```python
Network.objects.filter(code="net1").prefetch_related(
    Prefetch(
        "station_set",
        queryset=Station.objects.filter(code="stat1")
    )
).union(
    Network.objects.filter(code="net2").prefetch_related(
        Prefetch(
            "station_set",
            queryset=Station.objects.filter(code="stat25")
        )
    )
)

```

to automatically get translated to

```python
Network.objects.filter(code="net1").union(
    Network.objects.filter(code="net2")
).prefetch_related(
    Prefetch(
        "station_set",
        queryset=Station.objects.filter(
            Q(network__code="net1", code="stat1")
            | Q(network__code="net2", code="stat25")
        )
    )
)

```

likely isn’t given the complexity involved. In all cases ticket that requests clarifications on queryset combination (`union`, `intersection`, `difference`) interactions with `prefetch_related` would certainly be valuable.

---

<div class="post-metadata">

**Author:** ![cliff688](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/cliff688/32/22816_2.png) [@cliff688](https://forum.djangoproject.com/u/cliff688)\
**Post date:** [July 14, 2025, 10:11pm UTC](https://forum.djangoproject.com/t/using-union-with-querysets-that-have-a-custom-prefetch-object/41638/10 "2025-07-14T22:11:33Z")

</div>

> Allowing `prefetch_related` to be called after `union` (as long as `values` and `values_list` are not used) should be acceptable.

There’s already an [open ticket](https://code.djangoproject.com/ticket/28519) proposing support for applying base queryset methods to combined querysets like `union()`. That likely covers support for calling `prefetch_related()` after `union()` as well.

---

<div class="post-metadata">

**Author:** ![cliff688](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/cliff688/32/22816_2.png) [@cliff688](https://forum.djangoproject.com/u/cliff688)\
**Post date:** [July 14, 2025, 10:36pm UTC](https://forum.djangoproject.com/t/using-union-with-querysets-that-have-a-custom-prefetch-object/41638/11 "2025-07-14T22:36:42Z")

</div>

> In all cases ticket that requests clarifications on queryset combination (`union`, `intersection`, `difference`) interactions with `prefetch_related` would certainly be valuable.

Opened [#36507 (Document that only the base queryset’s prefetch\_related() is preserved when using union()) – Django](https://code.djangoproject.com/ticket/36507).
