Left outer join rather than subquery.

[Dan Davis]

Suppose you have a setup like this:

     class Journal(models.Model):

          title = models.CharField(max_length=200)

     issn_type_choices = (

         ('E', 'Electronic'),

         ('P', 'Print'),

     }

     class Issn(models.Model):

          ELECTRONIC='E'

          PRINT='P'

          journal = models.ForeignKey(Journal)

          issn_type = models.CharField(max_length=1)

          issn = models.CharField(max_length=10)

I can express annotations to assure I get botha  Physical and Print ISSN:

issn_p=Issn.objects.filter(issn_type='E', journal_id=OuterRef('id'))

issn_e=Issn.objects.filter(issn_type='P', journal_id=OuterRef('id'))

queryset = Journal.objects.annotate(issn_print=Subquery(issn_p), issn_electronic=Subquery(issn_e)).filter(issn_print__isnull=False, issn_electronic__isnull=False)

However, is there anyway for me to express two OneToOneField relationships from Issn to Journal to make sure a constraint is added at the database level and get easier querysets?

Thanks,

-Dan

[Dan Davis]

I'm going to answer myself in case anyone else looks for an answer to this.   In Django<2.x, the Subquery method I'm using is the best available.

In Django>=2, I can use the FilteredRelation with my annotation instead:

https://docs.djangoproject.com/en/2.1/ref/models/querysets/#django.db.models.FilteredRelation

The uniqueness constraint is actually already there in my actual model, and Django supports that through unique_together = ('journal', 'issn_type').