This is unfortunately not possible to do through the ORM right now due to lack of support for filtering against window expressions[0]
Until this ticket is solved you'll have to rely on raw SQL. Cheers, Simon [0] ticket https://code.djangoproject.com/ticket/28333 Le mercredi 4 novembre 2020 à 12:25:30 UTC-5, nhb.ra...@gmail.com a écrit : > Hi, > > In Django queryset you can query PuzzleAnswer, filter by author (assuming > that is the user), order_by('created)' and return the created dates only: > values_list('created', flat=True). Then take the len() of that list and > check the [0] and [-1] entries for first and last date. The rest is date > math. > > In general I try to keep the number of database accesses as low as > possible. A pre-calculated answer takes less resources to present than > calculating this over and over again, especially if you have many users. So > you could do the above and store the result in a new table every time a new > PuzzleAnswer has been added for a user. > > Ramon > > Op woensdag 4 november 2020 om 05:34:22 UTC+1 schreef Brad Buran: > >> I have a "puzzle of the day" that users can answer. I track the puzzle of >> the day using the Puzzle model. I track the answers using the PuzzleAnswer >> model. I would like to calculate the number of consecutive puzzles a >> particular user (i.e., the author) gets right in a row. The current SQL I >> use that can calculate the start date of the streak, end date of the streak >> and the number of days in the streak. As you can see, it does a dens_rank >> over the puzzles (to count them in order), then does a join with the >> PuzzleAnswer, then does a second dense rank over the merged tables. I >> figured out how to use the DenseRank function in the Django ORM on the >> Puzzle manager, but I cannot figure out how to do the left join next. Any >> advice? >> >> SELECT min(s.id) AS id, >> count(s.date) AS streak, >> min(s.date) AS start_streak, >> max(s.date) AS end_streak, >> s.author_id >> FROM ( SELECT dense_rank() OVER (ORDER BY ROW(pa.author_id, pr.rank)) >> AS id, >> pa.created AS date, >> (pr.rank - dense_rank() OVER (ORDER BY ROW(pa.author_id, >> pr.rank))) AS g, >> pa.author_id >> FROM (( SELECT "POTD_puzzle".id, >> dense_rank() OVER (ORDER BY "POTD_puzzle".published) >> AS rank >> FROM public."POTD_puzzle") pr >> JOIN public."POTD_puzzleanswer" pa ON ((pr.id = >> pa.puzzle_id))) >> WHERE pa.correct) s >> GROUP BY s.author_id, s.g >> ORDER BY count(s.date) DESC; >> >> The models are: >> >> class PuzzleAnswer(models.Model): >> puzzle = models.ForeignKey(Puzzle, editable=True, >> on_delete=models.CASCADE) >> >> answer = models.CharField(max_length=64) >> correct = models.BooleanField(editable=False) >> created = models.DateTimeField(auto_now_add=True) >> author = models.ForeignKey(settings.AUTH_USER_MODEL, blank=True, >> null=True, >> >> on_delete=models.SET_NULL) >> >> >> >> class Puzzle(models.Model): >> category = models.ForeignKey(PuzzleCategory, on_delete=models.CASCADE, >> help_text=category_help) >> notation = models.CharField(max_length=64) >> correct_answer = models.CharField(max_length=64) >> published = models.DateField(blank=True, null=True, db_index=True, >> unique=True) >> >> >> > -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/7282e7dc-e098-462e-b9aa-050dfcbde60dn%40googlegroups.com.
