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

Issue

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, related_name="hint_activity"))

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.

EDIT: So I realized that the answer given was exactly what I asked for. However, I messed up my question. The answer accepted returns the next Hint without a reference in the log as requested, but I need to it return the next Hint without reference for that user.

I tried to modify the query to:

queryset = Hint.objects.filter(hint_activity=None, chapter__url_key=chapter_uuid, hint_activity__user=curr_user).order_by('hint_level').first()
    

and

queryset = Hint.objects.filter( hint_activity__user=curr_user).filter(hint_activity=None, chapter__url_key=chapter_uuid, hint_activity__user=curr_user).order_by('hint_level').first()
    

Note: I added a reverse name to the GameActivity FK (related_name="hint_activity").

curr_user is the current logged in user making the call:

curr_user = self.request.user

Neither of these methods are returning any results.

Thanks for any help.

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.

Answered By - willeM_ Van Onsem