how to join in queryset-CodePudding

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 ?

CodePudding user response：

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.

CodePudding user response：

Add a new property to your PP model like this:

class PP(models.Model):
    imei = models.BigIntegerField(primary_key=True)
    name = models.CharField(max_length = 50)
    val2 = models.IntegerField(default = 0)
    @property
    def last_mea(self):
        return self.mea_set.order_by('date','id').last()
    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

With this new property you can access to last_mea doing:

pp.last_mea

So, in your templates, assuming a queryset of PP objects:

{% for q in queryset %}
    <tr>
        <td>{{q.imei}}</td>
        <td>{{q.name}}</td>
        <td>{{q.last_mea.v1}}</td>
        <td>{{q.last_mea.v2}}</td>
        <td>{{q.last_mea.v3}}</td>
        <td>{{q.last_mea.dates | date:'Y-m-d'}}</td>
    </tr>
{% endfor %}

EDIT:

You cannot show the time with this format: "2020-10-16 11:15:53" because you are only storing a date without time. The "dates" field type is "DateField" and not "DateTimeField". If you need the time, change DateField to DateTimeField and the default value (today --> now).