django_join.py

from django.db import models
from django.db import connection
from django.db.models.sql.datastructures import Join

class JoinQueryset(models.QuerySet):
    def join(self, qs=None):
        '''
        Either uses the current queryset and effectively does a self-join to
        create a new limited queryset OR it uses a querset given by the user.

        The model of a given queryset needs to contain a valid foreign key to
        the current queryset to perform a join. A new queryset is then created.
        '''

        if qs:
            fk = [
                fk for fk in qs.model._meta.fields
                if getattr(fk, 'related_model', None) == self.model
            ]
            fk = fk[0] if fk else None
            model_set = '{}_set'.format(self.model.__name__.casefold())
            key = fk or getattr(qs.model, model_set, None)

            if not key:
                raise ValueError('QuerySet is not related to current model')
            fk_column = key.column
            qs = qs.only(fk_column)
            # if we give a qs we need to keep the model qs to not lose anything
            new_qs = self
        else:
            fk_column = 'id'
            qs = self.only(fk_column)
            new_qs = self.model.objects.all()

        query = qs.query
        sql = '''
            DROP TABLE IF EXISTS temp_stuff;
            DROP INDEX IF EXISTS temp_stuff_id;
            CREATE TEMPORARY TABLE temp_stuff AS {query};
            CREATE INDEX temp_stuff_id ON temp_stuff (id);
        '''.format(query=str(query))

        with connection.cursor() as cursor:
            cursor.execute(sql)

        class TempModel(models.Model):
            temp_key = models.ForeignKey(
                self.model.__name__,
                on_delete=models.DO_NOTHING,
                db_column='id'
            )

            class Meta:
                managed = False
                db_table = 'temp_stuff'

        conn = Join(
            table_name=TempModel._meta.db_table,
            parent_alias=new_qs.query.get_initial_alias(),
            table_alias=None,
            join_type='INNER JOIN',
            join_field=self.model.tempmodel_set.field,
            nullable=False
        )
        new_qs.query.join(conn, reuse=None)
        return new_qs