Django ORM Queryset Problem

Using Django
1

Hi I have these models

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 Scores but with different Machines
This is really tricky and I am not sure how I can filter it using Django ORM.
Please help!

@KenWhitesell , any comments?

2

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.

3

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

4

It this case I think the correct approach is:

  1. Make the queryset with all scores you need and use values() or 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):

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:

<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:

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.

5

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

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

6

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

7

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:

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.

8

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

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.)

1 Like
9

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