How to refer to all child objects with existing queries in self-referencing foreign key?

user_4092125 0
Viewed 412

I have a model that represents an owner. There is a foreign key on this model to itself to represent a parent entity. There is another model called asset with a foreign key to owner. The purpose of the parent foreign key is to emulate a corporate structure, such that a parent “owns” an Asset whose foreign key is itself or a subsidiary:

Class Owner(models.Model):
    parent = models.ForeignKey(
                 “self”,
             )

Class Asset(models.Model):
    owner = models.ForeignKey(
                 Owner,
             )

Is there a way return all assets owned by a parent and all of its subsidiaries by simply referencing the parent (eg Asset.object.filter(owner=parent))? I know I can create a method to return a queryset of all subsidiaries of a parent, and then filter to all assets in that owner queryset, but I am hoping to avoid a large refactor given the existing code base doesn’t have the concept of a parent owner.

My first thought is a custom manager, but I don’t think that will change the behavior of existing queries that are all off the default manager. Can I overload filter on this model? If I need to rethink design that is fine, but I think this approach is cleaner and captures the behavior we want. Thank you!

django-models django
1 Answers

Possibly not a satisfactory or complete answer but I have a raw postgres query that returns all children for a parent in a single query. It uses a recursive query

class Owner(models.Model):
    parent = models.ForeignKey('self', on_delete=models.SET_NULL, null=True, blank=True)

    def get_all_children(self):
        return Owner.objects.raw(f"""
            with recursive t(id, parent_id) as
            (
                select id, parent_id
                from app_owner where id={self.id}
                union all
                select b.id, b.parent_id
                from app_owner b
                join t on b.parent_id=t.id
            )
            select * from t
        """)

This can be used to filter assets too

Asset.objects.filter(owner__in=Owner.objects.get(id=1).get_all_children())
user_5485620
Related