[Answered ]-How to query multiple Django models describing denormalized tables

by

2👍

✅

If your DB access allows this, I would probably do this by defining a Person model, then declare the name DB column to be a foreign key to that model with to_field set as the name on the person model. Then you can use the usual __ syntax in your queries.

Assuming Django doesn’t complain about a ForeignKey field with primary_key=True, anyway.

class Person(models.Model):
    name = models.CharField(primary_key=True, max_length=...)

class JobInfo(models.Model):
    person = models.ForeignKey(Person, primary_key=True, db_column='name', to_field='name')
    startdate = models.DateField(db_column='startdate')
    ...

I don’t think to_field is actually required as long as name is declared as your primary key, but I think it’s good for clarity. Or if you don’t declare name as the PK on person.

I haven’t tested this, though.

To use a view, you have two options. I think both would do best with an actual table containing all the known user names, maybe with a numeric PK as Django usually expects as well. Let’s assume that table exists – call it person.

One option is to create a single large view to encompass all information about a user, similar to the big join you use above – something like:

create or replace view person_info as 
    select person.id, person.name,
           jobinfo.startdate, jobinfo.employer, jobinfo.salary,
           hobbies.exercise, hobbies.fun,
           clothing.shoes, ...
    from person
         left outer join hobbies on hobbies.name = person.name
         left outer join jobinfo on jobinfo.name = person.name
         left outer join clothing on clothing.name = person.name
;

That might take a little debugging, but the idea should be clear.

Then declare your model with db_table = person_info and managed = False in the Meta class.

A second option would be to declare a view for each subsidiary table that includes the person_id value matching the name, then just use Django FKs.

create or replace view jobinfo_by_person as
    select person.id as person_id, jobinfo.*
    from person inner join jobinfo on jobinfo.name = person.name;
create or replace view hobbies_by_person as
    select person.id as person_id, hobbies.*
    from person inner join hobbies on hobbies.name = person.name;

etc. Again, I’m not totally sure the .* syntax will work – if not, you’d have to list all the fields you’re interested in. And check what the column names from the subsidiary tables are.

Then point your models at the by_person versions and use the standard FK setup.

This is a little inelegant and I make no claims for good performance, but it does let you avoid further denormalizing your database.

👤Peter DeGlopper

Leave a comment