Create a Django field which behaves like a ForeignKey but queries through a join?

Question

I have two database tables which are logically related through a CharField. For example, a survey response which asks for a phone number, and a person which has a phone number:

class SurveyResponse(Model):
    phone_number = CharField()
    favorite_brand_of_soda = CharField()
    person = JoinedOneToOneField("Person", from_field="phone_number", to_field="phone_number")

class Person(Model):
    phone_number = CharField()
    name = CharField()

Is it possible to create a Field — like the JoinedOneToOneField in this example — which would allow me to query for the Person who is related to the SurveyResponse, if one exists?

For example, so that I can use select_related to ensure that queries are efficient:

>>> responses = SurveyResponse.objects.all().select_related("person")
>>> print [ (r.person.name, r.favorite_brand_of_soda) for r in responses ]

And so that I can access the related Person through an attribute lookup:

>>> response = SurveyResponse.objects.get(…)
>>> print response.person.name

I know that ForeignKey accepts a to_field= parameter, but this won't quite do it because the database will try to enforce referential integrity through that field, which is inconsistent with the data model.

In other words, something similar to SQLAlchemy's relationships.

PLEASE NOTE: I do not want to use a FOREIGN KEY: the records that I'm dealing with are pencil-and-paper forms, and there is no guarantee that numbers will match. Before you suggest that I'm wrong in this, please consider that this is a simplified example, and the fact that I really, honestly, do know how to use a ForeignKey field.

Well. I would look at it this way : A person can give multiple survey responses. SO, I would implement it as a ForeignKey relationship. Is there any specific reason you want to avoid the referential integrity constraint ? — karthikr, Oct 12 '13 at 03:02
Because it's not consistent with the data model: people can answer the survey without being a `Person` in the database, and `People` in the database can exist without having answered a survey. — David Wolever, Oct 12 '13 at 03:09
Agreed, but you can make that nullable. Since the person and response are loosely coupled, have another person reference (nullable foreign key) ? — karthikr, Oct 12 '13 at 03:11
Ya, I could hack around it by adding a `person_id` `ForeignKey` to `Person`. But I want to see if it's possible to do without that. — David Wolever, Oct 12 '13 at 03:14
I see your point, and would encourage you to see alternatives, but I dont think it is a hack :) — karthikr, Oct 12 '13 at 03:17
Fair enough :) Although I would classify "denormalizing (or altering my data model) to appease Django" a bit of a hack. — David Wolever, Oct 12 '13 at 03:19
I would suggest that you are altering your data model to better represent the data you have. Explicit is better than implicit :-) — bpoetz, Oct 12 '13 at 17:28
I appreciate your suggestions that my data model is incorrect… but in this case, I'll have to defer to "trust me, I know what I'm doing here; there are forces at play which are more complex than the simplified example in my StackOverflow question" :) — David Wolever, Oct 12 '13 at 18:00
Fair enough, just my 2 cents based on the info given. FYI - ForeignKey does not accept the through= keyword, at least not in django 1.5. ManyToManyField does allow through, to specify a relationship table. You may be thinking of to_field. It may be worth looking at the to_field implementation to see if it allows arbitrary db column types. If it doesn't, I would suggest anything allowing for the syntax you suggest is going to be more trouble than it's worth. — bpoetz, Oct 12 '13 at 19:41
This question has no use case, example or requirements - other than no foreign keys allowed. It attempts to find a solution of a non-existing problem - since Django allows for many flexible and efficient queries. -1 — tutuDajuju, Oct 29 '13 at 18:17

score 2 · Accepted Answer · answered Oct 28 '13 at 05:52

OK! So after some intense digging, it doesn't seem like this is possible… at least in any sort of general way. Specifically, looking at fill_related_selections (which, as best as I can tell, is responsible for resolving select_related ), it it seems to use Options.get_fields_with_object()… but Options.get_fields_with_object only includes "local" fields (ie, fields which map to columns): https://github.com/django/django/blob/4dbd95ad/django/db/models/options.py#L328

This would appear to make creating any kind of JoinedForeignKey field, useable with select_related(…), impossible: such a field would need to be "virtual" (since it doesn't have a database column), but virtual fields aren't included in select_related queries :(

NOW! It would be possible to create a special case field which could join to exactly one other table… something like:

class SurveyResponse(Model):    
    phone_number = UglyJoinedForeignKey(
        CharField(…),
        fk_field="person",
        to=Person,
        to_field="phone_number",
    )

And it would need to be queried by the field to be joined on, not the name of the field used for the joined object:

SurveyResponse.objects.all().select_related("phone_number")

But… that doesn't quite seem worth it :\

Create a Django field which behaves like a ForeignKey but queries through a join?

1 Answers1