在一个复杂的结构化Django模型中使用aggregate的正确方法是什么

flmtquvp  于 2023-11-20  发布在  Go
关注(0)|答案(2)|浏览(162)

我有一个Django应用程序，它代表一个足球联赛，应该显示得分点和其他东西，我需要根据这个应用程序中的模型创建一个函数，以获得当前赛季的目标，积分，赢得的比赛和位置的总和，这里是我的模型：

models.py

  1. class TeamName(models.Model):
  2.     """
  3.     Stores Available team name to be picked later by users
  4.     """
  6.     name = models.CharField(max_length=33, verbose_name=_(
  7.         "Team Name"), help_text=_("Name of the team to be used by players"))
  8.     logo = models.FileField(upload_to="uploads", verbose_name=_(
  9.         "Logo Image"), help_text=_("The File that contains the team logo image"), null=True)
  11.     def image_tag(self):
  12.         """
  13.         This method created a thumbnil of the image to be viewed at
  14.         the listing of logo objects :model:'teams.models.TeamName'
  15.         """
  16.         return mark_safe(f'<img src="/uploads/{self.logo}" width="100" height="100" />')
  18.     image_tag.short_description = _("Logo")
  19.     image_tag.allow_tags = True
  21.     class Meta:
  22.         """
  23.         Defines the name of the model that will be viewied by users
  24.         """
  25.         verbose_name = _("1. Team Name")
  27.     def __str__(self) -> str:
  28.         """
  29.         Make sure to view the name as string not the id or pk
  30.         """
  31.         return str(self.name)
  33. class TeamStrip(models.Model):
  34.     """
  35.     Stores Available team Strip to be picked later by users
  36.     """
  37.     image = models.FileField(upload_to="uploads/uploads", verbose_name=_(
  38.         "Team Strips"), help_text=_("A Strip to be used later by users"))
  40.     def image_tag(self):
  41.         """
  42.         This method created a thumbnil of the image to be viewed
  43.         at the listing of logo objects :model:'teams.models.TeamLogo'
  44.         """
  45.         return mark_safe(f'<img src="/uploads/{self.image}" width="100" height="100" />')
  47.     image_tag.short_description = 'Image'
  48.     image_tag.allow_tags = True
  50.     class Meta:
  51.         """
  52.         Defines the name of the model that will be viewied by users
  53.         """
  54.         verbose_name = _("2. Team Strip")
  56. class Team(models.Model):
  57.     """
  58.     Stores Available teams 
  59.     """
  60.     name = models.ForeignKey(TeamName, on_delete=models.CASCADE, related_name="team_set_for_name",
  61.                              verbose_name=_("Team Name"), help_text="Name of the team")
  62.     home_strip = models.ForeignKey(TeamStrip, on_delete=models.CASCADE, related_name="team_set_for_home_teamstrip",
  63.                                    verbose_name=_(
  64.                                        "Team Home Strip"), help_text="Home Shirt for the team")
  65.     away_strip = models.ForeignKey(TeamStrip, on_delete=models.CASCADE, related_name="team_set_for_away_teamstrip",
  66.                                    verbose_name=_(
  67.                                        "Team Away Strip"), help_text="Away Shirt for the team")
  68.     league = models.ForeignKey("leagues.LeagueSeason", on_delete=models.CASCADE, related_name="team_set_for_league",
  69.                                null=True, verbose_name=_("League Season"),
  70.                                help_text=_("League season that team plays in "))
  71.     cap = models.ForeignKey("players.PlayerProfile", on_delete=models.CASCADE,
  72.                             related_name="team_set_for_cap_playerprofile",
  73.                             verbose_name=_("Team Captain"), help_text=_("Captain of the team"))
  74.     players = models.ManyToManyField("players.PlayerProfile", blank=True, verbose_name=_(
  75.         "Team Players"), help_text=_("Players that is playing in the team"), related_name="team_set_for_players")
  76.     average_skill = models.DecimalField(max_digits=5, decimal_places=2,
  77.                                         default=0, verbose_name=_(
  78.             "Average Team Skill"), help_text=_("An Average of Player's skills"))
  79.     points = models.PositiveIntegerField(default=0, verbose_name=_("Team Points"),
  80.                                          help_text=_("Team points in the current league season"))
  82.     def logo_tag(self):
  83.         """
  84.         This method created a thumbnil of the image to be viewed at
  85.         the listing of logo objects :model:'teams.models.TeamName'
  86.         """
  87.         return mark_safe(f'<img src="/uploads/{self.name.logo}" width="100" height="100" />')
  89.     logo_tag.short_description = _("Logo")
  90.     logo_tag.allow_tags = True
  92.     def home_strip_tag(self):
  93.         """
  94.         This method created a thumbnail of the image to be viewed at
  95.         """
  96.         return mark_safe('<img src="/uploads/%s" width="50" height="50" />' % (self.home_strip.image))
  98.     home_strip_tag.short_description = _("Home Strip")
  100.     def away_strip_tag(self):
  101.         """
  102.         This method created a thumbnail of the image to be viewed at
  103.         """
  104.         return mark_safe('<img src="/uploads/%s" width="50" height="50" />' % (self.away_strip.image))
  106.     away_strip_tag.short_description = _("Away Strip")
  108.     class Meta:
  109.         """
  110.         Defines the name of the model that will be viewed by users
  111.         Defines the ordering of queryset
  112.         """
  113.         verbose_name = _("3. Team")
  114.         ordering = ["-points"]
  116.     def __str__(self) -> str:
  117.         """
  118.         Make sure to view the name as string not the id or pk
  119.         """
  120.         return mark_safe(
  121.             f"{self.name.name} {self.league.region.name_ar} {self.league.region.name_en}")  # pylint: disable=maybe-no-member
  123.     def save(self, *args, **kwargs):
  124.         """
  125.         This method removes the saved  :model:'teams.models.TeamStrip' - :model:'teams.models.TeamLogo' - :model:'teams.models.TeamName' from the Regions :model:locations.models.Region
  126.         """
  127.         if not self.pk:
  128.             self.league.available_team_names.remove(
  129.                 self.name)
  130.         super(Team, self).save(*args, **kwargs)
  132.     @staticmethod
  133.     def autocomplete_search_fields():
  134.         """
  135.         This method used to define what fields to be searched by user in admin dashboard
  136.         """
  137.         return ("id__iexact", "name__name__icontains", "league__region__name_ar__icontains",
  138.                 "league__region__name_en__icontains",)
  140. class JoinRequest(models.Model):
  141.     """
  142.     Store available Join Requests 
  143.     """
  144.     status_choices = (
  145.         ('pending', 'pending'),
  146.         ('accepted', 'accepted'),
  147.         ('refused', 'refused'),
  148.     )
  149.     created = models.DateTimeField(auto_now_add=True, verbose_name=_(
  150.         "Created on"), help_text=_("Data the request created at"))
  151.     team = models.ForeignKey(Team, on_delete=models.CASCADE, related_name="joinrequest_set_for_team",
  152.                              verbose_name=_("Team"), help_text=_("Team the request to join sent to"))
  153.     player = models.ForeignKey("players.PlayerProfile", on_delete=models.CASCADE,
  154.                                related_name="joinrequest_set_for_playerprofile", verbose_name=_(
  155.             "Player"), help_text=_("Player that sent the request"))
  156.     status = models.CharField(choices=status_choices, max_length=33, default='pending', verbose_name=_(
  157.         "Status"), help_text=_("Status of the request"))
  159.     class Meta:
  160.         """
  161.         Defines the name of the model that will be viewied by users
  162.         """
  163.         verbose_name = _("4. Join Requests")
  165. class LeagueSeason(models.Model):
  166.     """
  167.     Saves League seasson in the current region
  169.     relations
  170.     -----------
  171.     :models:`locations.models.Region`
  172.     :models:`players.models.PlayerProfile`
  173.     :models:`teams.models.Team`
  174.     :models:`teams.models.TeamName`
  175.     :models:`teams.models.TeamLogo`
  176.     :models:`teams.models.TeamStrip`
  178.     """
  179.     status_choices = (
  180.         ('upcoming', 'upcoming'),
  181.         ('current', 'current'),
  182.         ('finished', 'finished'),
  183.     )
  184.     start_date = models.DateField(verbose_name=_(
  185.         "Start Date"), help_text=_("The date when seasson start"))
  186.     is_accepting = models.BooleanField(default=True, verbose_name=_(
  187.         "Is Accepting Teams"), help_text=_("True if the league still accepts teams"))
  188.     region = models.ForeignKey(Region, on_delete=models.PROTECT, verbose_name=_(
  189.         "Region"), help_text=_("The Region of the League seasson"))
  190.     players = models.ManyToManyField(PlayerProfile, verbose_name=_(
  191.         "Players"), help_text=_("PLayers in this league"))
  192.     teams = models.ManyToManyField('teams.Team', blank=True, verbose_name=_(
  193.         "Teams"), help_text=_("Teams in this League seasson"))
  194.     status = models.CharField(choices=status_choices, max_length=33, default='upcoming', verbose_name=_(
  195.         "Status"), help_text=_("Current Status of the league seasson"))
  196.     available_team_names = models.ManyToManyField(TeamName, verbose_name=_(
  197.         "Available Team Names"), help_text=_("Pickable Team Names in this seasson"))
  198.     available_team_strips = models.ManyToManyField(TeamStrip, verbose_name=_(
  199.         "Available Team Strips"), help_text=_("Pickable Team Strips in this seasson"))
  201.     class Meta:
  202.         """
  203.         Make sure to change the appearane name of 
  204.         the model :model:`league.models.LeagueSeasson` to be 
  205.         Seasson
  206.         """
  207.         verbose_name = _("Seasson")
  209.     def __str__(self) -> str:
  210.         """
  211.         Change the name that user see in the lists of :model:`leagues.models.LeagueSeasson` 
  212.         to be Region name then start date
  213.         """
  214.         return f"{self.region} - {self.start_date}"
  216. class Match(models.Model):
  217.     """
  218.     Saves matches data
  220.     Relations
  221.     ----------
  222.     :model:`teams.models.Team`
  223.     :model:`leagues.models.LeagueSeasson`
  224.     :model:`location.models.Location`
  225.     """
  226.     date_time = models.DateTimeField(verbose_name=_(
  227.         "Date and time"), help_text=_("Date and time of the match"))
  228.     home_team = models.ForeignKey('teams.Team', related_name="home_team_team", on_delete=models.PROTECT, verbose_name=_(
  229.         "Home Team"), help_text=_("Home Side team in the match"))
  230.     away_team = models.ForeignKey('teams.Team', related_name="away_team_team", on_delete=models.PROTECT, verbose_name=_(
  231.         "Away Team"), help_text=_("Away Side team in the match"))
  232.     league = models.ForeignKey(LeagueSeason, on_delete=models.CASCADE, null=True, verbose_name=_(
  233.         "League Seasson"), help_text=_("The Seasson of this match"))
  234.     location = models.ForeignKey('locations.Location', on_delete=models.PROTECT, null=True, verbose_name=_(
  235.         "Location"), help_text=_("Location where the match will be played"))
  237.     class Meta:
  238.         """
  239.         Changes the Appearance name of 
  240.         :model:`leagues.models.Match` to be Match
  241.         """
  242.         verbose_name = _("Match")
  244. class Goal(models.Model):
  245.     """
  246.     Saves goal records in every match
  247.     related to :model:`players.models.PlayerProfile` and :model:`teams.models.Team` .
  248.     """
  249.     match = models.ForeignKey(Match, on_delete=models.CASCADE, verbose_name=_(
  250.         "Match"), help_text=_("Match where the goal was scored"))
  251.     team = models.ForeignKey("teams.Team", on_delete=models.PROTECT, null=True,
  252.                              blank=True, verbose_name=_("Team"), help_text=_("The team scored the goal"))
  253.     player = models.ForeignKey('players.PlayerProfile', related_name="goal_maker", on_delete=models.PROTECT,
  254.                                null=True, blank=True, verbose_name=_("Player"), help_text=_("Player who scored the goal"))
  255.     assistant = models.ForeignKey('players.PlayerProfile', related_name="goal_assist", on_delete=models.PROTECT,
  256.                                   null=True, blank=True, verbose_name=_("Assist"), help_text=_("PLayer who assisted scoring this goal"))
  258.     class Meta:
  259.         """
  260.         Make sure to see the model  :model:`leagues.models.Goal` name 
  261.         as Goal for user
  262.         """
  263.         verbose_name = _("Goal")
  265.     def __str__(self) -> str:
  266.         """
  267.         Show the object name as the name of the player 
  268.         who scored the goal from :model:`users.models.AppUser` .
  269.         """
  270.         return f"{self.player.app_user.first_name} {self.player.app_user.last_name}"
  272. class Card(models.Model):
  273.     """
  274.     Saves Cards records in every match
  275.     related to :model:`players.models.PlayerProfile` , :model:`leagues.Match`and :model:`teams.models.Team` .
  276.     """
  277.     CARDS_ENUM = (
  278.         (_("Red Card"), _("Red Card")),
  279.         (_("Yellow Card"), _("Yellow Card")),
  280.     )
  281.     match = models.ForeignKey(Match, on_delete=models.CASCADE, related_name="card_set_for_match", verbose_name=_(
  282.         "Match"), help_text=_("Match where the goal was scored"))
  283.     team = models.ForeignKey("teams.Team", on_delete=models.PROTECT, null=True, blank=True,
  284.                              related_name="card_set_for_team", verbose_name=_("Team"), help_text=_("The team scored the goal"))
  285.     player = models.ForeignKey('players.PlayerProfile', on_delete=models.PROTECT, null=True, blank=True,
  286.                                related_name="card_set_for_playerprofile", verbose_name=_("Player"), help_text=_("Player who scored the goal"))
  287.     type = models.CharField(max_length=100, choices=CARDS_ENUM, verbose_name=_(
  288.         "Card Type"), help_text=_("Type of the card "))
  290.     class Meta:
  291.         """
  292.         Make sure to see the model  :model:`leagues.models.Card` name 
  293.         as Card for user
  294.         """
  295.         verbose_name = _("Card")
  297.     def __str__(self) -> str:
  298.         """
  299.         Show the object name as the name of the player 
  300.         who scored the goal from :model:`users.models.AppUser` .
  301.         """
  302.         return f"{self.player.app_user.first_name} {self.player.app_user.last_name} - {self.type}"

字符串
下面是我尝试实现的结果：

views.py

  1. @permission_classes(["IsAuthenticated"])
  2. @api_view(["POST"])
  3. def get_league_scores(request):
  4.     league_id = request.data["league_id"]
  6.     try:
  7.         season = LeagueSeason.objects.get(pk=league_id)
  8.     except LeagueSeason.DoesNotExist:
  9.         return Response({"error": "LeagueSeason not found"}, status=404)
  11.     teams = Team.objects.filter(league=season)
  13.     team_stats = []
  15.     for team in teams:
  16.         # Calculate team statistics
  17.         matches_played = team.home_team_team.filter(league=season).count() + team.away_team_team.filter(league=season).count()
  18.         matches_won = team.home_team_team.filter(league=season, home_team=team, home_team_goals__gt=F('away_team_goals')).count() + team.away_team_team.filter(league=season, away_team=team, away_team_goals__gt=F('home_team_goals')).count()
  19.         matches_lost = team.home_team_team.filter(league=season, home_team=team, home_team_goals__lt=F('away_team_goals')).count() + team.away_team_team.filter(league=season, away_team=team, away_team_goals__lt=F('home_team_goals')).count()
  20.         matches_tied = matches_played - (matches_won + matches_lost)
  21.         goals_scored = team.home_team_team.filter(league=season).aggregate(Sum('home_team_goals'))['home_team_goals__sum'] + team.away_team_team.filter(league=season).aggregate(Sum('away_team_goals'))['away_team_goals__sum']
  22.         points = (matches_won * 3) + matches_tied
  24.         team_stats.append(
  25.             {
  26.                 "team_name": team.name.name,
  27.                 "matches_played": matches_played,
  28.                 "matches_won": matches_won,
  29.                 "matches_lost": matches_lost,
  30.                 "matches_tied": matches_tied,
  31.                 "goals_scored": goals_scored,
  32.                 "points": points,
  33.             }
  34.         )
  36.     # Sort the teams based on points, from lowest to highest
  37.     sorted_team_stats = sorted(team_stats, key=lambda x: x["points"], reverse=True)
  39.     # Add position to each team based on the sorted order
  40.     for i, team_stat in enumerate(sorted_team_stats, start=1):
  41.         team_stat["position"] = i
  43.     return Response(sorted_team_stats, status=200)

型
当我尝试调用这个API时，我得到这个错误

  1. Cannot resolve keyword 'home_team_goals' into field. Choices are: away_team, away_team_id, card_set_for_match, date_time, goal, home_team, home_team_id, id, league, league_id, location, location_id

型

django

2条答案

按热度按时间
nwsw7zdq

nwsw7zdq1#

view.py中的此行代码

  1. season = LeagueSeason.objects.get(league_id)

字符串
我认为应该是：

  1. season = LeagueSeason.objects.get(pk=league_id)

型
这一行输入两次

  1. league_id = request.data.get('league_id')

型
models.py我不明白联赛赛季中的关系，似乎在模型中遗漏了一些东西。

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
3bygqnnd

3bygqnnd2#

我将通过向Match表添加一个赢家字段来对数据库进行一点非规范化：

  1. class Match(models.Model):
  2.     # Null if match was a draw.
  3.     winner = models.ForeignKey("teams.Team", null=True, blank=True, on_delete=models.SET_NULL)

字符串
您可以在保存模型示例或使用数据库触发器时填充该字段。
然后，查询简化为：

  1. matches = Match.objects.filter(Q(home_team=team) | Q(away_team=team), league=season)
  2. matches_played = matches.count()
  3. matches_won = matches.filter(winner=team).count()
  4. matches_tied = matches.filter(winner__isnull=True).count()
  5. matches_lost = matches_played - (matches_won + matches_tied)
  6. goals_scored = Goal.objects.filter(team=team, match__league=season).count()

型

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com