# Django ORM Queryset Problem

**URL:** <https://forum.djangoproject.com/t/django-orm-queryset-problem/9509>\
**Category:** Using Django\
**Created:** [September 1, 2021, 10:24am UTC](https://forum.djangoproject.com/t/django-orm-queryset-problem/9509 "2021-09-01T10:24:02Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![devspectre](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/devspectre/32/3559_2.png) [@devspectre](https://forum.djangoproject.com/u/devspectre)\
**Post date:** [September 1, 2021, 10:24am UTC](https://forum.djangoproject.com/t/django-orm-queryset-problem/9509/1 "2021-09-01T10:24:02Z")

</div>

Hi I have these models

```auto
class Score(models.Model):
    """"""

    id = models.BigAutoField(primary_key=True)
    venuemachine = models.ForeignKey(
        VenueMachine,
        related_name='scores',
        null=True,
        on_delete=models.CASCADE,
    )

    ...
    created = models.DateTimeField(auto_now_add=True)
    updated = models.DateTimeField(auto_now=True)

class VenueMachine(models.Model):
    """"""

    ...
    machine = models.ForeignKey(
        Machine,
        on_delete=models.CASCADE,
        related_name='venuemachines',
    )
    ...

class Machine(models.Model, ImageResizeMixin):
    """"""

    name = models.CharField(
        max_length=150,
        verbose_name='Machine Name',
    )

```

What I want is to get five latest **Score** s but with different **Machine** s  
This is really tricky and I am not sure how I can filter it using Django ORM.  
Please help!

@KenWhitesell , any comments?

---

<div class="post-metadata">

**Author:** ![Rigo-Villalta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/rigo-villalta/32/5667_2.png) [@Rigo-Villalta](https://forum.djangoproject.com/u/Rigo-Villalta)\
**Post date:** [September 1, 2021, 2:59pm UTC](https://forum.djangoproject.com/t/django-orm-queryset-problem/9509/2 "2021-09-01T14:59:04Z")

</div>

Hello @devspectre .

Lets say you have six machines:

VenueMachine1, VenueMachine2, VenueMachine3, VenueMachine4, VenueMachine5, VenueMachine6.  
Do you want?

1. The five most recent scores no matter what machine generated them?
2. The five most recent scores of five machines that are, one score per machine up to five machines?
3. The mos recent score of every machine?

For the first one Django ORM is the best solution, for the two last ones is better to get a Queryset and them work with it in Python. Please explain better what type of results do you need.

---

<div class="post-metadata">

**Author:** ![devspectre](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/devspectre/32/3559_2.png) [@devspectre](https://forum.djangoproject.com/u/devspectre)\
**Post date:** [September 1, 2021, 3:11pm UTC](https://forum.djangoproject.com/t/django-orm-queryset-problem/9509/3 "2021-09-01T15:11:44Z")

</div>

Hi @Rigo-Villalta  
Let’s shorten the words, Score = S, VenueMachine = VM, Machine = M

I have 10 scores and let’s assume these are orderes by updated time.  
S1, S2, S3, … S10

And they are made on several machines but also have duplicates.  
S1: VM1: M1  
S2: VM2: M2  
S3: VM3: M2  
S4: VM4: M3  
S5: VM5: M3  
S6: VM6: M3  
S7: VM7: M4  
S8: VM8: M4  
S9: VM9: M5  
S10: VM10: M6

So the correct result should be [S1, S2, S4, S7, S9]  
Hope this makes sense.

So `order_by` Score.updated and `distinct` by Score.venuemachine.machine

---

<div class="post-metadata">

**Author:** ![Rigo-Villalta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/rigo-villalta/32/5667_2.png) [@Rigo-Villalta](https://forum.djangoproject.com/u/Rigo-Villalta)\
**Post date:** [September 1, 2021, 5:18pm UTC](https://forum.djangoproject.com/t/django-orm-queryset-problem/9509/4 "2021-09-01T17:18:21Z")

</div>

It this case I think the correct approach is:

1. Make the queryset with all scores you need and use [values()](https://docs.djangoproject.com/en/3.2/ref/models/querysets/#values) or [values\_list()](https://docs.djangoproject.com/en/3.2/ref/models/querysets/#values-list) method, those will return a dictionary or a list with the values you query.
2. Iterate or use the Python build-in sorted() method in order to filter at Python level your data structure.
3. User the final data structure as context in your view.

For example (I haven’t test this in code, so it could have even misspelling or syntactic mistakes):

```auto
qs = Score.objects.select_related(
    "venuemachine__machine"
    ).values_list(
    "updated", "venuemachine_ name", "machine_name"
    ).oder_by("~updated", "venuemachine_name", "machine_name" )[10]

```

I am assuming that the model VenueMachine has a “name” field hat correspond to “VM1”, “VM2”… With that you will have a Queryset as list, more or least like this:

```auto
<Queryset [(S1, VM1, M1), (S2, VM2, M2), (S3, VM3, M2), (S4, VM4, M3), 
           (S5, VM5, M3), (S6, VM6, M3), (S7, VM7, M4), (S8, VM8, M4), 
           (S9, VM9, M5), (S10, VM10, M6)]

```

Know you can iterate over this list, for example:

```auto
used_m = []
scores = []
for score in qs:
    if score[2] in used_m:
        continue
    else:
        scores.append(score[0])
        used_m.append(score[2])

```

The list scores will contains the data you want as context in your view.

I hope it helps.

---

<div class="post-metadata">

**Author:** ![devspectre](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/devspectre/32/3559_2.png) [@devspectre](https://forum.djangoproject.com/u/devspectre)\
**Post date:** [September 1, 2021, 5:24pm UTC](https://forum.djangoproject.com/t/django-orm-queryset-problem/9509/5 "2021-09-01T17:24:10Z")

</div>

@Rigo-Villalta  
Well, that is not what I want.

It is sorted by updated which is correct but it still has duplicate machines

---

<div class="post-metadata">

**Author:** ![Rigo-Villalta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/rigo-villalta/32/5667_2.png) [@Rigo-Villalta](https://forum.djangoproject.com/u/Rigo-Villalta)\
**Post date:** [September 1, 2021, 5:25pm UTC](https://forum.djangoproject.com/t/django-orm-queryset-problem/9509/6 "2021-09-01T17:25:50Z")

</div>

You will have duplicated machines in the Queryset, but not in the final list. Read all the post please

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [September 2, 2021, 3:34am UTC](https://forum.djangoproject.com/t/django-orm-queryset-problem/9509/7 "2021-09-02T03:34:09Z")

</div>

So this one’s actually fairly easy - but it may need to change slightly depending upon what information you may need.

The basic query is this:

```auto
machines = Machine.objects.annotate(
  latest_score=Cast(
    Subquery(
      Score.objects.filter(
        venuemachine__machine=OuterRef('id')
      ).order_by('-updated').values('id')[:1]
    ),
    output_field=IntegerField()
  )
).annotate(
    updtd=Max('venuemachines __scores__ updated')
).order_by('-updtd')[:5]

```

This gives you the 5 unique Machine with the latest scores, and, if you need the score object itself, each Machine object is annotated with an attribute “latest\_score” with the pk of the Score object.

(You could turn _this_ into a subquery to return a queryset of the Score objects directly.)

There’s probably an even easier way to handle this, but I’m not seeing it at the moment.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [September 2, 2021, 3:45am UTC](https://forum.djangoproject.com/t/django-orm-queryset-problem/9509/8 "2021-09-02T03:45:02Z")

</div>

I did find an easier way if you’re looking for a queryset returning Score:

```auto
Score.objects.filter(
   updated__in=Subquery(
     Machine.objects.annotate(
       updtd=Max('venuemachines __scores__ updated')
     ).order_by('-updtd')[:5].values_list('updtd')
   )
 ).order_by('-updated')

```

This is assuming you don’t have any two Score with identical updated times. If that assumption doesn’t hold, this won’t work. (But then you would need to determine whether you want to arbitrarily exclude items if there is a tie for the end of the list.)

---

<div class="post-metadata">

**Author:** ![devspectre](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/devspectre/32/3559_2.png) [@devspectre](https://forum.djangoproject.com/u/devspectre)\
**Post date:** [September 2, 2021, 6:08am UTC](https://forum.djangoproject.com/t/django-orm-queryset-problem/9509/9 "2021-09-02T06:08:48Z")

</div>

@KenWhitesell  
That assumption is correct.  
No scores have same updated times.