Apologies in advance if this question is hard to follow – I'm not sure how to phrase it. Basically, I'm trying to create a sort of "pseudo-field" in a Django model that works exactly like any other Django field except that it's actually a reference to a field on a related model.
As an example, suppose I am running a hotel for dogs. In my hotel, I have rooms, and each room is assigned to a customer and contains a dog.
class Customer(Model):
name = models.CharField(max_length=256, null=False)
class Dog(Model):
name = models.CharField(max_length=256, null=False)
customer = model.ForeignKey(Customer, null=True, on_delete=models.CASCADE) # The dog's owner
class Room(Model):
customer = model.ForeignKey(Owner, null=True, on_delete=models.SET_NULL)
dog = model.ForeignKey(Dog, null=True, on_delete=models.SET_NULL)
The corresponding tables in the database look something like this
CUSTOMER:
| ID | NAME |
___________________
| 01 | John Smith |
| 02 | Jane Doe |
DOG:
| ID | NAME | CUSTOMER_ID |
____________________________
| 01 | Rover | 01 |
| 02 | Fido | 01 |
| 03 | Spot | 02 |
ROOM:
| ID | DOG_ID | CUSTOMER_ID |
_____________________________
| 01 | 01 | 01 |
| 02 | 03 | 02 |
So, my boss notices that we're storing redundant data in the DB: the rooms table doesn't really need to have its own customer ID column: the customer is always the resident dog's owner, and each room contains only one dog, and each dog has one owner, so we can always get the customer assigned to the room by going to the dog table and looking up the owner. I've been asked to remove the customer ID column from the rooms table in a way that is "totally transparent" to the rest of the code base.
To start with, I can convert customer into a @property in the Room class, with a custom getter:
class Room(Model):
dog = model.ForeignKey(Dog, null=True, on_delete=models.SET_NULL)
@property
def customer(self):
return self.dog.customer
So now everywhere in the code where we do something like c = room.customer, it continues to work. The problem is, the code base is full of Django field queries like room__customer, which all stop working when I make customer into a @property of Room. I could change them all to room__dog__customer, which works fine, but then the change wouldn't be "totally transparent".
I did some research and I've tried implementing a custom manager and annotating the query set for Rooms:
class RoomManager(models.Manager):
def get_queryset(self):
return super().get_queryset().annotate(customer=F('dog__customer'))
When I do that, I can use room__customer in queries, but not room__customer__name, I suppose because F() is returning the primary key value rather than a model instance (https://docs.djangoproject.com/en/2.1/ref/models/expressions/#using-f-with-annotations).
So my question is, is there a way to make this work that I just don't know about? A way to make it so that Room acts like it has a direct foreign key relationship with Customer, without having customer_id stored in the rooms table?