Pfthb

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?

I'm guessing your manager's line about "totally transparent" is simply a way to say "whatever you change shouldn't screw up other business logic." In other words, the application should continue to work as it does today. I doubt very much that he cares how many files you edit (though, he could be a micro-manager, in which case ... I'm sorry).

That said, I think your solution of changing room__customer to room__dog__customer is the best course of action. The change makes it obvious to other Django developers what's going on (you're walking the relationship), and it's a fairly straightforward change. Yes, you may end up having to touch a number of files, but such is life when you muck around with the backend schema.

One thing you need to consider with this change, however, is potential performance implications. You may need to introduce select_related calls to your queries, to ensure that tables are joined properly. Otherwise, doing the room -> dog -> customer lookups may become expensive (with an extra database round trip per lookup; this really adds up in a loop).

Best of luck!