Subquery in Django query

Question

Django

Good afternoon!
Already asked a question - it was necessary to make custom sorting based on another table.
habrahabr.ru/qa/29605/ - in fact, the diagram is here.
I decided quite simply - I first filter HabrItem, do a join with the ItemOrdering table, then filter ItemOrdering with the same parameters, take a query from HabrItem and replace the table mention in it with a query from ItemOrdering and make a query to the database through raw ()
What is the method bad? The fact that I completely lack the necessary methods from the manager and queryset, and I use the pijinator and standard views of the list_view type.
I can’t figure out how to get around this or implement it differently yet ...
Here is how it looks in the code.

class ParamOne(models.Model):
    title = models.CharField(verbose_name=u'title', max_length=32)
    def __unicode__(self):
        return self.title

class ParamTwo(models.Model):
    title = models.CharField(verbose_name=u'title', max_length=32)
    def __unicode__(self):
        return self.title

class ParamThree(models.Model):
    title = models.CharField(verbose_name=u'title', max_length=32)
    def __unicode__(self):
        return self.title


class Item(models.Model):
    title = models.CharField(verbose_name=u'title', max_length=32)
    paramOne = models.ManyToManyField(ParamOne, blank=True, null=True)
    paramTwo = models.ManyToManyField(ParamTwo, blank=True, null=True)
    paramThree = models.ManyToManyField(ParamThree, blank=True, null=True)

    objects = ItemManager()

    def __unicode__(self):
        return self.title

class ItemOrdering(models.Model):
    item = models.ForeignKey(Item)
    paramOne = models.ForeignKey(ParamOne, blank=True, null=True)
    paramTwo = models.ForeignKey(ParamTwo, blank=True, null=True)
    paramThree = models.ForeignKey(ParamThree, blank=True, null=True)
    order = models.IntegerField(verbose_name=u"Order")


def getOrdered():
    paramOne = ParamOne.objects.get(title=1)
    paramTwo = ParamTwo.objects.get(title=1)
    itemOrdering = ItemOrdering.objects.filter(paramOne=paramOne, paramTwo=paramTwo).exclude(paramThree=None)

    items = Item.objects.filter(paramOne=paramOne, paramTwo=paramTwo)
    items.query.join(('items_item', 'items_itemordering', 'id', 'item_id'), promote=True, nullable=True)
    items = items.extra(select={'ordering':'COALESCE("items_itemordering".order, "items_item".order)'}, order_by=['ordering'])


    objcts = Item.objects.raw(items.query.__str__().replace('JOIN "items_itemordering"', 'JOIN (%s) AS "items_itemordering"'%itemOrdering.query.__str__()))
    return objcts

And here is the sql result

SELECT (COALESCE("items_itemordering".order, "items_item".order)) AS "ordering", "items_item"."id", "items_item"."title" FROM "items_item" INNER JOIN "items_item_paramOne" ON ("items_item"."id" = "items_item_paramOne"."item_id") INNER JOIN "items_item_paramTwo" ON ("items_item"."id" = "items_item_paramTwo"."item_id") LEFT OUTER JOIN (SELECT "items_itemordering"."id", "items_itemordering"."item_id", "items_itemordering"."paramOne_id", "items_itemordering"."paramTwo_id", "items_itemordering"."paramThree_id", "items_itemordering"."order" FROM "items_itemordering" WHERE ("items_itemordering"."paramOne_id" = 2  AND "items_itemordering"."paramTwo_id" = 1  AND NOT ("items_itemordering"."paramThree_id" IS NULL))) AS "items_itemordering" ON ("items_item"."id" = "items_itemordering"."item_id") WHERE ("items_item_paramOne"."paramone_id" = 2  AND "items_item_paramTwo"."paramtwo_id" = 1 ) ORDER BY "ordering" ASC

How can this be solved through the standard orm?
I already did a mankipatching and was able to insert a subquery into .extra(tables=[]), but it still breaks everything, because then it uses this query in the table name everywhere (

Answer 1

It may be easier to break it into separate small requests than to try to pull everything out in one bundle, generating monstrous joins, which will most likely take longer to execute anyway.