Prefetch Related with Multiple Column Join

[Ram Jayaraman]

I apologize if this is an old question, I did search SO/google for a good few hours.

I have the following models.

Publisher()

  name = CharField()

Medium()

  name = CharField()

Article()

  publisher = ForeignKey(Publisher, related_name="articles")

  medium = ForeignKey(Medium, related_name="articles")

  text = TextField()

PublisherMediumBilling()

  publisher = ForeignKey(Publisher()

  medium = ForeignKey(Article)
  lots_of_other_columns

 ...
  class Meta:
      unique_together = ['publisher', 'medium']

I am looking to query all Article from a given publisher, and also fetch the related PublisherMediumBilling joining on the columns Publisher and Medium, Prefetch() object doesn't seem to quite work for this case.

I can of course filter-query for all PublisherMediumBilling and cache it and do a in-memory Python JOIN. Is there a way to do through one of the ORM Apis ?

[Simon Charette]

Hi Ram,

I don't have a complete solution for you but I suggest you take a look at the
django.db.models.fields.related.ForeignObject class.

If I'm not mistaken it should allow you to define multi-column relationships
on both your Article and PublisherMediumBilling models and refer to them using
the `select_related()` and `prefetch_related()``API while the later uses Python
to perform in-memory JOINs.

class Article(models.Model):

    publisher = ForeignKey(Publisher, related_name='articles')

    medium = ForeignKey(Medium)
    billing = ForeignObject(
        'PublisherMediumBilling', from_fields=['publisher', 'medium'], to_fields=['publisher', 'medium']
    )

publisher.articles.select_related('billing')

Cheers,
Simon

[Ram Jayaraman]

Hi Simon,

Thanks for the post. I just tried this and ForeignObject requires one of the 2 columns being referred to be unique. But thats an interesting object!

[Simon Charette]

Hi Ram,

As PublisherMediumBilling.publisher and medium are unique together it's safe to
silence the `fields.E310` check failure in this case.

By the way the check was adjusted in Django 1.10 to also take `unique_together`
into account[1].

Cheers,
Simon

[1] https://github.com/django/django/commit/80dac8c33e7f6f22577e4346f44e4c5ee89b648c

[Ram Jayaraman]

Hi Simon,

Thanks a lot for the response again! I really appreciate your time. I saw the uniq_together but didn't realize checks can be silenced in Django. ( `0 silenced` in the log should have been a clue! ). I silenced it and it does work great. 

I am not sure if I missed this in the Django docs but this is a really nice feature for many situations once the models start getting a bit crazy. I feel this should should be emphasized a bit more in the Docs. I am pretty terrible at docs so can't offer much help apart there, so it would be great if the Django docs team can add this. Should I make a ticket for the same ?

Thanks again

Ram

[Simon Charette]

Hi Ram,

I suggest you open a new feature request to document ForeignObject as a
public API.

Cheers,
Simon