I have 3 models in the Django project:

# Железо class Hardware(models.Model): inventory_number = models.IntegerField(unique=True,) # Подразделение class Subdivision(models.Model): name = models.CharField(max_length=50,) # Перемещения class Relocation(models.Model): hardware = models.ForeignKey('Hardware',) subdivision = models.ForeignKey('Subdivision',) relocation_date = models.DateField(verbose_name='Дата перемещения', default=date.today())

Table 'Hardware_Relocation' with values ​​for the example:

 id hardware subdivision relocation_date 1 1 1 01.01.2009 2 1 2 01.01.2010 3 1 1 01.01.2011 4 1 3 01.01.2012 5 1 3 01.01.2013 6 1 3 01.01.2014 7 1 3 01.01.2015 # Сейчас железяка с id 1 хранится в подразделении 3 потому что дата перемещения максимальная

I would like to write a filter to search for iron in a particular unit at the moment. Those. need to know for example, what iron is in this unit right now.

I tried to write a filter:

 subdivision = Subdivision.objects.get(pk=1) hardware_list = Hardware.objects.annotate(relocation__relocation_date=Max('relocation__relocation_date')).filter(relocation__subdivision = subdivision)

Now hardware_list contains hardware 1, but this is not true (because hardware 1 is now in division 3). hardware_list should be None in this example.

The following code also does not work correctly ( hardware_list contains hardware 1, for unit 1).

 limit_date = datetime.datetime.now() q1 = Hardware.objects.filter(relocation__subdivision=subdivision, relocation__relocation_date__lte=limit_date) q2 = q1.exclude(~Q(relocation__relocation_date__gt=F('relocation__relocation_date')), ~Q(relocation__subdivision=subdivision)) hardware_list = q2.distinct()

Please help. Is it possible to solve the problem without using views, only using Django?

  • order_by to help Hardware.objects.filter(...).order_by('-relocation_date') - Vitali Falileev

1 answer 1

 subdivision = Subdivision.objects.get(pk=1) hardware_list = Hardware.objects.annotate( latest_relocation_date=Max('relocation__relocation_date') ).filter( relocation__relocation_date=F('latest_relocation_date'), relocation__subdivision=subdivision )

It is understood that there will not be several Relocation one Hardware in one day. Otherwise, if the Relocation.id the later Relocation always greater than the earlier Relocation , then:

 subdivision = Subdivision.objects.get(pk=1) hardware_list = Hardware.objects.annotate( latest_relocation_date=Max('relocation__relocation_date') ).filter( relocation__relocation_date=F('latest_relocation_date'), ).annotate( latest_relocation_id=Max('relocation__id') ).filter( relocation__id=F('latest_relocation_id'), relocation__subdivision=subdivision )

However, in this case, you can do without relocation_date :

 subdivision = Subdivision.objects.get(pk=1) hardware_list = Hardware.objects.annotate( latest_relocation_id=Max('relocation__id') ).filter( relocation__id=F('latest_relocation_id'), relocation__subdivision=subdivision )