Work-around to Django's ManyToManyField foreign key limitations

Question

I have a Django application that must use a database provided by another source. I am not able to change the schema. Everything below was generated by inspectdb:

class Family(models.Model):
    persistanceid = models.BigIntegerField(primary_key=True)

class Spouseref(models.Model):
    persistanceid = models.BigIntegerField(primary_key=True)

class Family_spouseref(models.Model):
    family_persistanceid = models.ForeignKey(Family, db_column='family_persistanceid')
    wiferefs_persistanceid = models.ForeignKey(Spouseref, unique=True, null=True, db_column='wiferefs_persistanceid', blank=True, related_name='wiferefs')
    husbandrefs_persistanceid = models.ForeignKey(Spouseref, unique=True, null=True, db_column='husbandrefs_persistanceid', blank=True, related_name='husbandrefs')
    childrefs_persistanceid = models.ForeignKey(Spouseref, unique=True, null=True, db_column='childrefs_persistanceid', blank=True, related_name='childrefs')

A ManyToManyField won't work under Family, since the mapping table, Family_spouseref, has multiple foreign keys to Spouseref. Given this schema, what's the most efficient way to accomplish something like this:

fam = Family.objects.get(persistanceid=id)
husbands = fam.husbands.all()
wives = fam.wives.all()
children = fam.children.all()

Update:

As requested... the following functions are what I tried to add within the Family model above.

I tried raw SQL like so:

    def husbands(self):
        return Spouseref.objects.raw ('SELECT * FROM spouseref WHERE spouseref.persistanceid IN (SELECT husbandrefs_persistanceid FROM family_spouseref WHERE family_persistanceid=%s);', [self.persistanceid])

Which works, but is raw SQL really the best way? I lose out on a lot of the usual object methods this way, it seems like.

When I tried doing it through the ORM, it kept wanting to think that the Family_spouseref table had a column id, which it clearly does not.

Family_spouseref.objects.get(family_persistanceid=self.persistanceid)

Produced:

[DEBUG] django.db.backends: (0.001) SELECT "family_spouseref"."id", "family_spouseref"."family_persistanceid", "family_spouseref"."wiferefs_persistanceid", "family_spouseref"."husbandrefs_persistanceid", "family_spouseref"."childrefs_persistanceid" FROM "family_spouseref" WHERE "family_spouseref"."family_persistanceid" = 45036  LIMIT 21; args=(45036,)
DatabaseError: column family_spouseref.id does not exist
LINE 1: SELECT "family_spouseref"."id", "family_spouseref"."family_p...

I also tried:

Family_spouseref.objects.get(Q(family_persistanceid=self.persistanceid) | Q(husbandrefs_persistanceid__isnull=False))

Which produced the same ID error but did attempt to follow the FK relationship:

[DEBUG] django.db.backends: (0.000) SELECT "family_spouseref"."id", "family_spouseref"."family_persistanceid", "family_spouseref"."wiferefs_persistanceid", "family_spouseref"."husbandrefs_persistanceid", "family_spouseref"."childrefs_persistanceid" FROM "family_spouseref" LEFT OUTER JOIN "spouseref" ON ("family_spouseref"."husbandrefs_persistanceid" = "spouseref"."persistanceid") WHERE ("family_spouseref"."family_persistanceid" = 45036  OR "spouseref"."persistanceid" IS NOT NULL) LIMIT 21; args=(45036,)
DatabaseError: column family_spouseref.id does not exist
LINE 1: SELECT "family_spouseref"."id", "family_spouseref"."family_p...

I'm also horrified to see by the debug how often it's calling the same (bad) statement (8 times) vs. the raw SQL, which it calls twice. (Still, twice!)

I really would like to know the official Django way for traversing these tables rather than ignore the ORM entirely and do raw SQL. Thanks.

Answer 1

The error you are getting is because Django set by default an "id" field as primary key, if no field has "primary_key=True" declared as argument (you did that in Family model for instance). So when you try to get your model objects, you will always get an error like this:

DatabaseError: column family_spouseref.id does not exist

First of all, give that model a primary key. I bet it has one. If no, maybe you just want to read this. Now, about your question of efficiency, you can do this:

def husbands(self):
    husbandrefs_for_family = Family_spouseref.objects.filter(
        family_persistanceid=self.persistanceid,
    ).values_list('husbandrefs_persistanceid',flat=True)

    return Spouseref.objects.filter(
        persistanceid__in=husbandrefs_for_family,
    )

Although it seems that you are doing 2 queries, you will see that Django just make one (well, it's a query with a subquery like the one you did in SQL).

The values_list part of the subquery is used to bring just the columns of the table you want. I'ts the select husbandrefs_persistanceid part of the SLQ statement you want.

Hope it helps!