# Append extra objects to a reverse ForeignKey queryset

**URL:** <https://forum.djangoproject.com/t/append-extra-objects-to-a-reverse-foreignkey-queryset/31360>\
**Category:** Using the ORM\
**Created:** [May 21, 2024, 7:43am UTC](https://forum.djangoproject.com/t/append-extra-objects-to-a-reverse-foreignkey-queryset/31360 "2024-05-21T07:43:02Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![marc-white](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/marc-white/32/21437_2.png) [@marc-white](https://forum.djangoproject.com/u/marc-white)\
**Post date:** [May 21, 2024, 7:43am UTC](https://forum.djangoproject.com/t/append-extra-objects-to-a-reverse-foreignkey-queryset/31360/1 "2024-05-21T07:43:02Z")

</div>

Hi all,

I’m porting an app across from Ruby on Rails, so I’m trying to work around the existing model/database structure **that I can’t alter** , and have come across an interesting conundrum.

Consider the following skeleton model structure:

```auto
class Requisition(models.Model):
    ...

class Item(models.Model):
    requisition_id = models.ForeignKey(Requisition, null=False)
    ...

class Event(models.Model):
    parent_item = models.ForeignKey(Item, null=True)
    parent_req = models.ForeignKey(Requisition, null=True)
    ...

    def clean(self):
        # Guarantee exactly one of parent_item and parent_req are populated

```

Therefore, an `Event` can be attached to either an `Item` or a `Requisition`, but not both simultaneously, and not neither. `Item`s always have a parent `Requisition`.

For any given `Item`, the relevant `Event`s are those which are attached to both the `Item`, and its parent `Requisition` (via the `requisition_id` foreign key). In standard query terms, this can be accessed a couple of ways, e.g.:

```auto
i = Item.objects.select_an_item_somehow()

# Filter Event object queryset
Event.objects.filter(Q(parent_item=i) | Q(parent_req=i.requisition_id ))

# Do a union of related manager querysets
i.event_set.union(i.requisition_id.event_set) # Recall no duplicates due to Event.clean()

```

However, this leads to a large number of database queries, especially if I need to do this for every `Item` I get back in some larger collection of those objects.

I need to do a fair bit of analysis/arithmetic using the `Event` objects relevant to any given `Item` (and often, for a large number of `Item`s all at once), so accessing this set easily is important.

What I’d really like to do is give the `Item` queryset the ability to give me the relevant `Event` queryset back as an integral part of the `Item` queryset (i.e., have the relevant queryset of `Event`s available as, say, `Item.relevant_event`). This strikes me as the most efficient way to handle this problem, in a vainglorious attempt to avoid hammering the database with multiple queries.

I’ve thought of a couple of different ways to attempt this, but haven’t had any luck so far:

1. **Use annotate.** Within the ItemQuerySet, I’ve tried to add an additional helper function that adds the relevant `Event` queryset into the `Item` queryset via `annotate`. However, I’ve not managed to get the right arrangement of `Subquery`, `Q` filtering, etc, to make this work. I’ve also realized I have no clue what field type this should return, so I’m not at all sure this is technically possible. My current attempt (which spits out a single number, so is obviously giving me nonsensical garbage) is something like:

```auto
class ItemQuerySet(models.QuerySet):
    def compute_relevant_events(self):
        # Attempts at prefetch, etc. optimization omitted
        return self.annotate(
            relevant_event=Subquery(
                    Event.objects.filter(
                        Q(parent_item=OuterRef("pk"))
                        | Q(parent_req=OuterRef("requisition_id__pk"))
                    ).values("pk")
                )
        )

```

1. **Alter the reverse relationship manager.** After spending far too long attempting the above, I realized it may be quicker to alter whatever manages the reverse lookup of `Item.event_set` to either:
  - Automatically add the `Event` objects from the parent `Requisition.event_set` into the return of `Item.event_set`; or,
  - Define a new reverse relationship call, say `Item.all_event_set`, that provides the same return.

To expand this idea, I presume that the SQL that is behind `Item.event_set` just does:

```auto
SELECT * FROM event WHERE event.parent_item=Item.pk

```

I’m wondering how I can alter that SQL (via Django magic) so that it becomes:

```auto
SELECT * FROM event WHERE event.parent_item=Item.pk OR event.parent_req=Item.requisition_id

```

Having pored through the documentation, I still can’t find if this is possible, or how to go about it. The documentation talks about [using a custom reverse manager](https://docs.djangoproject.com/en/5.0/topics/db/queries/#using-a-custom-reverse-manager), but unless I’ve missed something obvious, it doesn’t say if you can apply this automatically to a model (as opposed to invoking it for specific queries), nor what these custom reverse managers look like in practice.

If you’ve made it this far, thanks for reading my tome. Has anyone encountered this sort of problem before, and if so, did you manage to solve it?