django query: groupby and keep rows that are the most recent-CodePudding

I am struggling on a query that is supposed to group by a model attribute and return for each unique value of that model attribute, the row that has the most recent date.

I can't manage to get the output that I am looking for in a way that will be digestible for my template.

here is the model I am trying to query:

class Building(models.Model):

    building_id = models.AutoField(primary_key=True)
    created_at = models.DateTimeField(auto_now_add=True, blank=True)
    project = models.CharField(max_length=200,blank=True, null=True)

and here are my attempt #1: get the unique projects and use a for loop:

projects = list(Building.objects.all().values_list('project', flat=True).distinct())
context = {}
for project in projects:
    proj = Building.objects.filter(project=project).latest("created_at")
    context[project] = proj

this is ok but it does not allow me to access the queryset as {% for i in projects %} in template => causing pk problems for links

my other attempt tries to get rows, to keep everything in a queryset:

projects = Building.objects.all().values_list('project', flat=True).distinct()
proj = Building.objects.filter(project__in=list(projects)).latest("created_at")

that only returns the latest entered row regardless of the project name. This is close to what I am looking for, but not quite since I try to output the latest entered row for each unique project value.

what is the proper way to do that?

CodePudding user response：

You can work with:

{% for building in projects.values %}
    {{ buiding }}
{% endfor %}

or if you need the project as well:

{% for project, building in projects.items %}
    {{ buiding }}
{% endfor %}

CodePudding user response：

you can use only the latest() without any parameters and will get the latest entry to the table

try

proj = Building.objects.filter(project__in=list(projects)).latest()