Join and query Django models on non-primary-key relationship?

Question

I've got two models that are logically related through a field that is not the primary key. Is it possible to query them (ex, select_related(…)) without introducing a ForeignKey column?

For example, consider the contrived models:

class LogEntry(Model):
    source_name = CharField(…)
    log_message = CharField(…)

class LogSource(Model):
    name = CharField(…)
    domain = CharField(…)

I would like to be able to query LogEntry, joining in and filtering on the related LogSource (ex, so I can access log_entry.source without additional queries):

LogEntry.objects
    .select_related(
        source=Join(LogSource, on="logsource.name = logentry.source_name")),
    )
    .filter(source__domain="example.com")

Is this possible without introducing a ForeignKey?

Answer 1

You should be able to do this by using extra() with the tables option.

LogEntry.objects.extra(
    tables=['logsource'],
    where=['logsource.name=logentry.source_name',
           'logsource_domain="example.com"',
           ]
)

Another option is to change source_name to a foreign key, but specify the db_column and to_field arguments to use the existing columns. I know that you said that you didn't want to add a foreign key, but it might be acceptable because it only changes the models, not the columns in the database tables. However, be aware that Django might want to create a foreign key constraint. One hack would be to fake that migration so that the constraint isn't created in the db.

class LogEntry(Model):
    source_name = models.ForeignKey(db_column=source_name', to_field='name')

log_entry.source_name would then be the LogSource instance, and log_entry.source_name_id would be the value stored in the source_name column. It might make sense to rename the field from source_name to source after converting to a foreign key, but that's not necessary.