Join on Subquery with multiple columns

32 views
Skip to first unread message

Richard

unread,
Jun 27, 2019, 5:59:08 PM6/27/19
to Django users
I have the following model (not actual, but enough for sample purposes):

class TransactionLine(models.Model):
   txn_id
= models.IntegerField()
   entity
= models.CharField(max_length=10, null=True, default=None)
   order
= models.PositiveIntegerField()


what I'm trying to do is "find the first line in a transaction, that has a non-null entity when ordered by 'order'". In SQL (and django), I can use a group by to get which line order has the first non-null entity:

select
    txn_id
,
    min
(`order`)
from transactionline
where
    entity
is not null
group by txn_id
order
by txn_id, `order`

However, that query alone doesn't give me the entity for that line. This is where I get stuck in django, because in SQL I can do:

select
   t
.txn_id, t.entity, t.order
from transactionline t
inner join
(
    select
        txn_id
,
        min
(`order`) as ordering
   
from transactionline
   
where
        entity
is not null
   
group by txn_id
    order
by txn_id, `order`
) t1 on t.txn_id=t1.txn_id AND t.order = t1.ordering

But I haven't found how I can join on a subquery in django that has multiple conditions in the ON clause.

Is there anyway to achieve this without dropping to raw sql?

aside: Using Django 2.1 (can't upgrade to 2.2 because we have a hard dependency on pymysql)


Thanks!

Reply all
Reply to author
Forward
0 new messages