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!
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/e5f09520-36aa-4168-966d-14f895259094%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
