[FIXED] how to join in queryset

Issue

I have some problems with QuerySet in Django python. I have 2 tables/models.

class PP(models.Model):
    imei = models.BigIntegerField(primary_key=True)
    name = models.CharField(max_length = 50)
    val2 = models.IntegerField(default = 0)

    def __str__(self):
        return self.name

class Mea(models.Model):
    imei = models.ForeignKey(PP, on_delete=models.CASCADE)
    v1 = models.IntegerField(default = 0)
    v2 = models.IntegerField(default = 0)
    v3 = models.IntegerField(default = 0)
    dates = models.DateField(default=datetime.date.today)

    def __str__(self):
        return self.imei

PP
    +----+--------+------+
    |imei|  name  | val2 |
    +----+--------+------+
    |  1 | john   |   12 |
    |  2 | adam   |    5 |
    |  3 | alfred |    3 |
    +----+--------+------+



Mea
+----+----+----+-----+---------------------+
|imei| v1 | v2 | v3  |        date         |
+----+----+----+-----+---------------------+
|  1 |  4 | 15 |  18 | 2020-10-16 11:15:53 |
|  1 |  2 | 12 |  17 | 2020-10-16 11:22:53 |
|  1 |  3 | 13 |  16 | 2020-10-16 11:32:53 |
|  2 |  1 | 16 |  15 | 2020-10-16 13:22:53 |
|  2 |  3 | 13 |  25 | 2020-10-16 13:42:53 |
|  2 |  4 | 12 |  35 | 2020-10-16 14:12:53 |
|  3 |  1 | 21 | 12  | 2020-10-16 14:12:53 |
|  3 |  2 | 28 | 42  | 2020-10-16 15:12:53 |
|  3 |  4 | 30 | 72  | 2020-10-16 16:12:53 |
+----+----+----+-----+---------------------+

I need to get in one table ID, NAME, v1,v2,v3,date for the new date to all object from first table something like this:

RESULT
+----+--------+----+----+-----+---------------------+
|imei|  name  | v1 | v2 | v3  |        date         |
+----+--------+----+----+-----+---------------------+
|  1 | john   |  3 | 13 |  16 | 2020-10-16 11:32:53 |
|  2 | adam   |  4 | 12 |  35 | 2020-10-16 14:12:53 |
|  3 | alfred |  4 | 30 | 72  | 2020-10-16 16:12:53 |
+----+--------+----+----+-----+---------------------+

I need the newest record from SECOND TABLE for all people from first table.

Its posible to do it by queryset ?

Solution

Provided all PP have one or more related mea (i.e. no nulls) then I think you can filter the mea table to exclude duplicates, and you won't miss any PP rows without one or more related Mea rows.

qs = Mea.objects.order_by('-dates'      # descending, so newest first
   ).distinct( 'imei'                   # first row only for each imei 
   ).order_by( 'imei'                   # re-order if required. 
   ).select_related('imei'              # for efficiency
   )

for mea_instance in qs:
    ...

The data from pp is accessed via mea_instance.imei.name etc. The .select_related('imei') ought to cache the other object so you don't get N further DB queries.

Answered By - nigel222