Django use select_related () with multiple foreign key columns

I have a multi-application application that uses shared tables for all tenants (there is only one database and only one schema).

My tables store objects from remote systems of different clients, and all of these objects have an effective primary key (tenant, remote_id):

class Tenant(models.Model):
    company_name = models.TextField()

class Post(models.Model):
    tenant = models.ForeignKey(Tenant)
    remote_id = models.IntegerField()

    class Meta:
        unique_together = (('tenant', 'remote_id'))
        index_together = (('tenant', 'remote_id'))

      

        
        
        
      

    

(So ​​technically, I have auto-incrementing integer primary keys, but that doesn't really matter to my question, and I could delete them if it mattered.)

Among themselves, objects refer to each other using a remote identifier, the tenant is always the same (no cross-references between tenants):

class Comment(models.Model):
    tenant = models.ForeignKey(Tenant)
    remote_id = models.IntegerField()
    post_remote_id = models.IntegerField()

    class Meta:
        unique_together = (('tenant', 'remote_id'))
        index_together = (('tenant', 'remote_id'))

      

        
        
        
      

    

Django doesn't support multi-valued foreign keys, so I couldn't declare my "foreign keys" as such.

Now when you fetch objects from my local database, I would like to get the external objects bound to a key in the same query via a SQL JOIN. Django's select_related () is usually used for this , but since I did not declare them as foreign keys, I cannot use that.

Here's an example of what I would like to do (and have it hit the database with just one request!):

comments = Comment.objects.filter(author='Ernest').select_related()
commented_post_titles = [comment.post.title for comment in comments]

      

        
        
        
      

    

Is there some other way to get something equivalent in a clean way? I could override the manager class and execute native SQL using JOIN. But how can I cleanly instantiate the model and link them together without breaking the entire Django model system?

My preferred solution would of course be a kind of subclass models.ForeignKey

that behaves exactly like a normal foreign key (supporting select_related (), move, etc.), but takes into account the additional column condition tenant

. Could something like this be written?

To emphasize: this is a simple 1-to-n relationship. The referenced object may not yet exist in my local database (which is why I am not converting these relations to artificial foreign keys in the first place), but it is guaranteed that they will be exactly 0..1 related objects, not multiple.