Django - how can i filter a list of objects checking a foreign key attribute?

Question

First of all sorry for my bad English.

I have to filter a query but using a foreign key attribute. I have a WorkOrder model and have other model with the order movements.

this is the model

class OrderMovements(models.Model):
    workorder = models.ForeignKey(
            WorkOrder,
            verbose_name=_('order'),
        )
    status = models.IntegerField(
            choices=FULL_CHOICES,
            default=1,
            verbose_name=_('status'),
        )
    timestamp = models.DateTimeField(auto_now_add=True, auto_now=False)

Well like you see a work order can have a lot of movements, the queryset that i need to make is a query that take the last 10 WorkOrders that have a OrderMovement with status 3 or 4 included in any of the movements of that WorkOrder.

I really don't know how can a make this.

Hope you can understand me.

Thanks!

Answer 1

As usual, you start with the model you want to get, and follow the relationships via double underscores.

WorkOrder.objects.order_by('-timestamp').filter(ordermovements__status__in=[3, 4])[:10]

Answer 2

First use related_name to easily perform reverse relation look ups

class OrderMovements(models.Model):
    workorder = models.ForeignKey(
        WorkOrder,
        related_name='ordermovements',
        verbose_name=_('order'),
    )
    // rest code

Then you can query workorder and use related_name for reverse relationship filter.

Also you require distinct to avoid duplicates

Your query should be:

WorkOrder.objects.order_by('-timestamp').filter(
    ordermovements__status__in=[3, 4]).distinct('-timestamp')[:10]