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 [email protected].
To post to this group, send email to [email protected].
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.
