user is a foreign key on tournament.
select u.id, u.display_name, count(t.id)
from tournament t join user u
on t.user_id = u.id
where date(t.start_date)> '2022-07-01'
group by u.display_name, u.id
How can I make the above SQL query work with django's ORM?
In the majority of cases trying to translate an sql query into Django ORM syntax isn't the way to go.
From what i understand, you want to count tournaments, filtered with a date, bound to an user.
Try something like:
UserModel.objects.annotate(tournament_count=Count("tournament", filter=Q(start_date__gt=my_date)))
The annotate method allows for additionnal columns to be present in the ResultSet moslty related or calculated ones. ("tournament" is name of your Tournament model, if you defined a related_name for the user FK, use this name instead)
If you really want a group by, take a look at this How to query as GROUP BY in django?
