Django Query input results of one queryset to find an object that doesn't appear in another model

Good afternoon,

I am trying to write an DRF endpoint that returns the next unused hint.

I have two models:

class GameActivity(models.Model):
    activity_datetime = models.DateTimeField(default=timezone.now)
    user = models.ForeignKey(User, on_delete=models.CASCADE, null=True, blank=True, related_name='activity_user')
    activity_type = models.ForeignKey(GameActivityType, on_delete=models.CASCADE, null=True, blank=True,related_name='game_activity_type')
    activity_hint = models.ForeignKey(Hint, on_delete=models.CASCADE, null=True, blank=True)

And

class Hint(models.Model):
    hint_title = models.CharField(max_length=50)
    hint_detail = models.CharField(max_length=300)
    hint_level = models.IntegerField()
    chapter = models.ForeignKey(Chapter, on_delete=models.CASCADE, null=True, blank=True, related_name="chapter_hint")
    pts_cost = models.IntegerField()

What I am trying to do is return the next hint for the passed in Chapter ID that is NOT in the GameActivity model.

pseudo-code
return Hint where (Count (GameActivity where activity_hint=Hint AND Hint.Chapter == Chapter.ID) = 0) order by hint_level ASC LIMIT 1

I cannot figure out how to chain two queries together, the first being input to the second.

Queryset = SELECT * from Hint WHERE chapter.id = CHAPTER_ID

Pass in the queryset into GameActivity and return the one Hint with the lowest hint_level that doesn’t have a GameActivity entry.

Thanks for any help, I feel like I am not thinking about the problem correctly.

BCBB

>Solution :

You can obtain this with:

Hint.objects.filter(gameactivity=None, chapter_id=my_chapter_id).order_by(
    'hint_level'
).first()

this will return a Hint not referred to by any GameActivity for a given capter_id, and with the lowest hint_level. If there is no such item, None is returned.