Django ORM - how can I build the following query?

Question

models.py

class Box(models.Model):
    length  = models.IntegerField(null=False)
    width   = models.IntegerField(null=False)
    height  = models.IntegerField(null=False)

class BoxColorHistory(models.Model):
    box            = models.ForeignKey(Box, related_name='color_history')
    start_datetime = models.DateTimeField(null=False)
    end_datetime   = models.DateTimeField(null=True)
    color          = models.CharField(max_length=20)

I would like to write a query that returns all Boxes that have current color='green'.

The problem is that the color is constantly changing. Each time the color changes, this is recorded in the BoxColorHistory table.

The current color of the box is the row in the BoxColorHistory table with the most recent start time and an end time of null.

Iain Shelvington · Accepted Answer · 2014-08-15 16:01:30Z

1

current_green_histories = BoxColorHistory.objects.filter(
    end_datetime__isnull=True,
    color='green'
)
Box.objects.filter(color_history__id__in=current_green_histories.values('id'))

answered Aug 15, 2014 at 16:01

Iain Shelvington

32.5k3 gold badges36 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tadasajon Over a year ago

Thanks. I'm a newbie and confused, though -- where does color_history come from? The Box only has length, width, height fields.

crhodes · Accepted Answer · 2014-08-15 15:55:55Z

0

Try:

Box.objects.filter(color_history__color='green')

answered Aug 15, 2014 at 15:55

crhodes

1,1879 silver badges20 bronze badges

1 Comment

tadasajon Over a year ago

Every box has been green at some point. I just want the ones that are green now.

Collectives™ on Stack Overflow

Django ORM - how can I build the following query?

models.py

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

models.py

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related