Join django ORM

Question

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 the following query:

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

Can this be done without hacking with Django ORM? thanks!

Answer 1

select * ... will take more work. If you can crop the right columns on the right side

table=SomeModel._meta.db_table
join_column_1=SomeModel._meta.get_field('field1').column
join_column_2=SomeModel._meta.get_field('field2').column
join_queryset=SomeModel.objects.filter()
# Force evaluation of query
querystr=join_queryset.query.__str__()
# Add promote=True and nullable=True for left outer join
rh_alias=join_queryset.query.join((table,table,join_column_1,join_column_2))
# Add the second conditional and columns
join_queryset=join_queryset.extra(select=dict(rhs_col1='%s.%s' % (rhs,join_column_2)),
    where=['%s.%s = %s.%s' % (table,join_column_2,rh_alias,join_column_1)])

Add additional columns available for dict selection.

Additional restrictions are combined in WHERE after ON (), which your SQL engine may not optimize well.

Answer 2

I believe that Django ORM does not support the connection on anything that is not specified as a ForeignKey (at least the last time I looked at it, it was a limitation. They always add functions, so maybe it made its way) .

, - , , SQL-.

SQL- "". Django SQL-.