0

I have models as below:

class ProjectRecord(models.Model):
    user = models.ForeignKey(User,on_delete=models.CASCADE, null=True, blank=True,related_name='user_projects')
    project = models.ForeignKey(Project,on_delete=models.CASCADE, null=True, blank=True, related_name='user_projects')
    project_type = models.CharField(max_length=30, choices=(("project_a","A"),("project_b","B")),null=True)
    version = models.FloatField(null=True, blank=True)

I want to filter the latest value of version with this:

project = list(ProjectRecord.objects.filter(
    user=self.request.user, project_type='project_a'
))

But I don't know how to achieve it.the data in database is similar as below:

id project_id version project_type
1   5          1.0      project_a
2   5          1.0      project_b
3   4          1.0      project_a
4   4          1.0      project_b
5   5          2.0      project_a          
6   5          2.0      project_b
7   5          3.0      project_a
8   5          3.0      project_b

For example, I want to get the latest value of project_id=5 to exact match other data and do not delete other project_id's value if their versions are not updated, the queryset should be display as below

id project_id version project_type
1   4          1.0      project_a
2   4          1.0      project_b
3   5          3.0      project_a
4   5          3.0      project_b
markwalker_
  • 12,078
  • 7
  • 62
  • 99
Elsa
  • 1
  • 1
  • 8
  • 27

1 Answers1

1

Try to use annotation with Max:

from django.db.models import Max

ProjectRecord.objects.filter(
    user=self.request.user,
    project_type='project_a'
).values(
    'project_id', 'project_type'
).annotate(
    max_version=Max('version')
)
markwalker_
  • 12,078
  • 7
  • 62
  • 99
neverwalkaloner
  • 46,181
  • 7
  • 92
  • 100
  • Hi, thanks for your prompt answer, it seems like working, but on the front end, I can't get the name and datetime of project with userproject.project.name and userproject.project.name or any other changes, it has displayed normally before. – Elsa Jun 09 '18 at 08:45
  • 1
    @Begin2Pip Unfortunatly `values('project_id', 'project_type')` will limit fields you have access to `'project_id', 'project_type'`. So in case you need project detals you need to do additional query. Alternatively you can try to add required fields as values arguments directly: `values('project_id', 'project__name', 'project__datetime', 'project_type')`. – neverwalkaloner Jun 09 '18 at 08:49
  • Thank you so much, I want to display project.version too, only projectrecord.version, not projectrecord.project.version, I found this also limit field of version, right – Elsa Jun 09 '18 at 08:55
  • 1
    @Begin2Pip You are welcome. Not sure I understand you right. but instead of `version` you can access `max_version` field, which was annotated to the queryset. – neverwalkaloner Jun 09 '18 at 08:59
  • 1
    neverwalkaloner Yes, yes, max_version is right, thank you so so so much, – Elsa Jun 09 '18 at 09:54