Self join with django ORM

Question

Hello, everybody!

I have a model:

class Trades(models.Model):  
    userid     = models.PositiveIntegerField(null=True, db_index=True)
    positionid = models.PositiveIntegerField(db_index=True)
    tradeid    = models.PositiveIntegerField(db_index=True)
    orderid    = models.PositiveIntegerField(db_index=True)  
    ...

and I want to execute next query:

select *
from trades t1
inner join trades t2
ON t2.tradeid = t1.positionid and t1.tradeid = t2.positionid

can it be done without hacks using Django ORM? Thx!

Answer 1

I believe Django's ORM doesn't support doing a join on anything that isn't specified as a ForeignKey (at least, last time I looked into it, that was a limitation. They're always adding features though, so maybe it snuck in).

So your options are to either re-structure your tables so you can use proper foreign keys, or just do a raw SQL Query.

I wouldn't consider a raw SQL query a "hack". Django has good documentation on how to do raw SQL Queries.