For a small project, I have a registry of matches and results. Each match between teams (may be one player), and has a winner. Therefore, I have Match and Team models combined by MatchTeam . It looks like this (simplified) see below notes
class Team(models.Model): ... class Match(models.Model): teams = ManyToManyField(Team, through='MatchTeam') ... class MatchTeam(models.Model): match = models.ForeignKey(Match, related_name='matchteams',) team = models.ForeignKey(Team) winner = models.NullBooleanField() ...
Now I want to make some statistics on matches, starting with a search who is the one who hits you the most. I'm not quite sure how to do this, at least not efficiently.
In SQL (just approximating here) I would call something like this:
SELECT their_matchteam.id, COUNT(*) as cnt FROM matchteam AS your_mt JOIN matchteam AS their_mt ON your_mt.match_id = their_mt.match_id WHERE your.matchteam.id IN <<:your teams>> your_matchteam.winner = false GROUP BY their_matchteam.team_id ORDER BY cnt DESC
(This also requires the sentence "their_mt is not your_mt" btw, but the concept is clear, right?)
Until I tested this as SQL, just let me know what I'm looking for: I want to find this result using Django aggregation.
According to the manual, I can annotate the results with aggregation, in this case a Count . MatchTeams directly to MatchTeams , as I do in SQL, maybe a little shortcut, maybe there should be a “ Match ” between them? At least I don't know how to translate this to Django
So maybe I need to find specific matches for my team and then annotate them with a different team count? But what is the “other team”?
A quick record would look like this:
nemesis = Match.objects \ .filter(matchteams__in=yourteams) \ .annotate(cnt=Count('<<otherteam>>')).order_by('-cnt')[0]
If this is the correct track, how do I determine Count here. And if this is not the right way, what is it?
As is, all this applies to teams, not users. It's easy to keep things simple :)
An additional question might be: should I even do this with this Django ORM stuff, or is it better for me to just add SQL? This has an obvious flaw that you are stuck in writing very general code (is this possible?) Or patching your database server. If not necessary, I would like to avoid this.
About the model: I really want to understand what I can change about the model to make it better, but I can’t see a solution without flaws. Let me explain:
I want to support matches with an arbitrary number of teams, therefore, for example, a match with 5 teams. This means that I have a many-to-many relationship, and not one, which, for example, corresponds to 2 teams. If so, you can denormalize and put winners / points in the team table. But this is not so.
Additional data on the results of one team (for example, their final result, their time), by definition, are a property of the relationship. It cannot get into the Team table (since this would be a match, and you could have the number of matches undefined), and it cannot go to the match table for the same reason mutatis mutandis.
Example: I have teams A, B, C, D and E that play a match. Team A and team B have 10 points, the rest have 0 points. I want to keep the number of points, and that team A and team B are the winners of this match.
Thus, for comments suggesting that I need a “better” design, by all means, if you have one, I will love to see it, but if you want to support what I support, it will be difficult.
And as a final note: this data can be easily restored to SQL, so the model seems beautiful to me: I just start too much in Django to be able to do it in Django ORM!