Django filter queryset and get unique items-CodePudding

I am using Django and SqLite.

I have this model table:

class TestPROD(models.Model):
    prodID    = models.CharField(max_length=15)
    ProdDate  = models.CharField(max_length=15)
    Price1    = models.DecimalField(max_digits=10, decimal_places=3,default=0)
    Price2    = models.DecimalField(max_digits=10, decimal_places=3,default=0)
    post_date = models.DateTimeField(default=timezone.now)
    author    = models.ForeignKey(User,on_delete=models.CASCADE)
    def __str__(self):
        return self.prodID
    def get_Total(self):
        TotPRC =self.Price1  self.Price2
        return round(TotPRC, 2) 
    def get_absolute_url(self):
        return reverse('Custom', args=[self.prodID])
    class Meta:
        ordering = ('-TestDate',)

in my data base I have many rows which contains duplicated items for example prodID HP or Lenovo have 1000 or more rows.

my question is how to filter the data and give me only the last ProdDate of the product HP and so the last item for Lenovo and one.

in my views.py I tried many solution but I din't get the good results

def welltests(request):
        testprodms = TestPROD.objects.annotate(latest_test_date=('prodID')).order_by('-ProdDate').distinct()
        return render(request, 'Home/AllProd.html', {'testprodms': testprodms})

this will gives my all the data (50000 rows) but organized by product.

the unique prodID in my database is about 600. so I need 600 rows of different product with the last ProdDate

then I tried this and many other but not working;

def welltests(request):
    testprodms = TestPROD.objects.order_by().values_list('prodID', flat=True).distinct()
    return render(request, 'Home/AllProd.html', {'testprodms': testprodms})

this gives me the number of my products but with no other data in my HTML page

CodePudding user response：

You can use the distinct() method and the latest() method to filter the data and return only the last ProdDate of the product HP and the last item for Lenovo.

First, use the distinct() method to remove duplicate rows based on the prodID field:

TestPROD.objects.distinct('prodID')

Then, use the latest() method to return the last ProdDate for each prodID:

TestPROD.objects.distinct('prodID').latest('ProdDate')

You can also use the filter method to filter the data and get the last item for the specific product for example for HP:

TestPROD.objects.filter(prodID='HP').latest('ProdDate')

You can chain the filter and the latest together to get the desired result.

This will return a queryset containing the last ProdDate for each distinct prodID.

CodePudding user response：

After trying many solutions that I want to get a solution in one line but I wasn't able to get. sorry, so I get solved this using 3 lines.

def welltests(request):
    testprodms = TestPROD.objects.order_by().values_list('prodID', flat=True).distinct()
    AllProdcts = TestPROD.objects.all()
    listtests  =  [AllProdcts.filter(prodID__exact=prodtest).first() for prodtest in testprodms]
    return render(request, 'Home/AllProd.html', {'testprodms': listtests})