I have 3 models:

class Chemicals(models.Model):
    id_chemical = models.AutoField(primary_key=True)
    id_supplier = models.ForeignKey(Suppliers, null=True, on_delete = models.CASCADE)
    description = models.CharField(max_length=50, blank=False, null=False)
    [...]

class Suppliers(models.Model):
    id_supplier = models.AutoField(primary_key=True)
    company_name = models.CharField(max_length=100, blank=False, null=False)
    [...]

class Prices(models.Model):
    id_chemical=models.ForeignKey(Chemicals, null=False, on_delete = models.CASCADE, related_name='prezzo')
    price=models.IntegerField(blank=False, null=False, default=0)
    price_date=models.DateField(default=datetime.date.today)
    

I need to get the latest price by date and use it in a queryset to show it in a template. I tried this solution:

def price_list(request,pk):
    supplier = get_object_or_404(Suppliers, pk=pk)
    chemicals_list = Chemicals.objects.filter(id_supplier=pk)
    chem_price = chemicals_list
    qs = Prices.objects.all()

    prova = qs.values('id_chemical').annotate(latest_price=Max('price_date'))       
    qs = qs.filter(price_date__in=prova.values('latest_price').order_by('-  price_date')).filter(id_chemical__id_supplier=pk)

    context = {'supplier': supplier, 'chemicals_list': chemicals_list, 'qs': qs, 'chem_price': chem_price}
    return render(request, "chemicals/price_list.html", context)

Now I have what I need but if I want to show it in a template I have some problem. I tried to do this:

<tbody>
{% for chemical in chem_price %}
<tr>                                    
<td><a href="{% url 'chemicals:single-product' pk=chemical.id_chemical %}">{{ chemical.description }}</a></td>                            
<td>{{ qs.price }}</td>                                                      
<td>{{ chemical.cov }}</td>
{% endfor %}
</tbody>

Whit this solution, in template I see the same price for all products. I need to take some columns of my chemicals model, add latest price and show in a table row in a template.

CodePudding user response：

There are multiple points which I noticed that may need to be considered for better understanding of your code to yourself and potentially help you solve your problem as well.

• Class names should not be in plural form, as a class definition indicates a single entity (instance) of itself. So instead of class Chemicals, use class Chemical, and similarly everywhere.

• The id_chemical attribute that you intend to use as primary key of your class Chemicals, can be simply named as id, as the attribute itself is inside Chemicals class, therefore we don't need to mention it in its attribute name too. Similarly for other non-relation attributes.

• The id_supplier attribute that you intend to use as foreign key of your class Chemicals for relation with class Suppliers, can be simply named as supplier, as when you'll access this foreign key for an instance of Chemical, the attribute can be used to access the attributes of the related Supplier instance. So, for instance, instead of looking like the following

  chemical = Chemicals.objects.get(id=pk)
  return chemical.id_supplier.company_name
  

  it will look better and will be more understandable like following

  chemical = Chemical.objects.get(id=pk)
  return chemical.supplier.company_name
  

Similarly, there can be other improvements which will make your code more readable and understandable to everyone, and especially to you.

Now coming to your actual problem. Considering the recommendations in the aforementioned points and assuming that I've understood your problem correctly, your html code may need the following changes to work properly

• You're not closing your <tr> tag.
• There's no chem_price object defined anywhere
• The chemical object that you're using in for loop is actually a class Price object, so can't use {{chemical.description}} directly.

Other factors, like improper context data can also affect your desired outcome.

CodePudding user response：

I found a possible solution. In my Chemicals model, I put a method to get the last price

class Chemicals(models.Model):
     [...]

     def get_price(self):
        price_object = Prices.objects.all()     
          partial_qs=price_object.values('id_chemical').annotate(latest_price=Max('price_date'))
        price_object=price_object.filter(price_date__in=partial_qs.values('latest_price').order_by('-price_date')).get(id_chemical=self.id_chemical)
        price = price_object.price        
        return price  

Then, in my template:

{% for chemical in chemicals_list %}
     <tr>                                    
     <td><a href="{% url 'chemicals:single-product' pk=chemical.id_chemical %}">{{ chemical.description }}</a></td>                                                   
     <td>{{ chemical.get_price }}</td>                            
     <td>{{ chemical.cov }}</td>
     </tr>
     {% endfor %}

I hope this may be the right way. Now I have what I was searching for. Thanks for the suggestions you gave me.