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!
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())