calculated fields with foreign keys
Hi!
I have the following models:
class Player(models.Model):
name = models.CharField('Name', max_length=40)
pub_date = models.DateTimeField('date added',
default=datetime.datetime.now)
def __unicode__(self):
return self.name
def goals(self):
g = 0
ms1 = Match.objects.filter(opp1=self)
ms2 = Match.objects.filter(opp2=self)
for m in ms1:
g += m.opp1_goals
for m in ms2:
g += m.opp2_goals
return g
class Match(models.Model):
opp1 = models.ForeignKey(Player)
opp2 = models.ForeignKey(Player)
opp1_goals = models.IntegerField("Goals P1")
opp2_goals = models.IntegerField("Goals P2")
pub_date = models.DateTimeField('date published',
default=datetime.datetime.now)
The problem is when I want to display a statistics overview with all
players there will be 2 DB-Queries per player only to get the "goals"
property.
And I got more "calculated fields" like number of won matches, number of
lost matches, etc...
There are 364 Match entries and 46 Players.
At my overview page I got over 2000 SQL queries which take over 6 seconds
to load the page.
How can I speed up the process?
I don't want to save those "calculated values" in the database.
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/django-users/-/RsK3HmYOBBwJ.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
reduce number of DB-Queries
Hi!
I have the following models:
class Player(models.Model):
name = models.CharField('Name', max_length=40)
def __unicode__(self):
return self.name
def goals(self):
g = 0
ms = Match.objects.select_related().filter(
Q(opp1=self)|Q(opp2=self) )
for m in ms:
if (m.opp1==self):
g += m.opp1_goals
else:
g += m.opp2_goals
return g
def matches_won(self):
ms = Match.objects.filter( (Q(opp1=self) &
Q(opp1_goals__gt=F('opp2_goals'))) |
(Q(opp2=self) & Q(opp2_goals__gt=F('opp1_goals'))) ).count()
return ms
def matches_draw(self):
ms = Match.objects.filter( (Q(opp1=self) &
Q(opp1_goals__exact=F('opp2_goals'))) |
(Q(opp2=self) & Q(opp2_goals__exact=F('opp1_goals'))) ).count()
return ms
def match_points(self):
p = 0
p += 3 * self.matches_won()
p += 1 * self.matches_draw()
return p
def matches_played(self):
return Match.objects.filter(Q(opp1=self) | Q(opp2=self)).count()
class Match(models.Model):
class Meta:
verbose_name_plural = "Matches"
opp1 = models.ForeignKey(Player, related_name="player1")
opp2 = models.ForeignKey(Player, related_name="player2")
opp1_goals = models.IntegerField("Goals P1")
opp2_goals = models.IntegerField("Goals P2")
def __unicode__(self):
return "%s %d:%d %s"%(self.opp1.name, self.opp1_goals,
self.opp2_goals, self.opp2.name)
My problem is when want to display overall player statistics I get about
1200 DB-Queries.
This is because for each player there will be 5 Queries due to matches_won,
matches_draw, goals, matches_played functions.
How is it possible to retrieve those "calculated properties" without having
so much queries?
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/django-users/-/-ki6lNP0IDAJ.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
Re: reduce number of DB-Queries
Wow thanks! That's really going fast now. I would have never come to that idea :) One more question: Is this how you meant to set the instance variable or did you mean in another way? def __init__(self, *args, **kwargs): super(Player, self).__init__(*args, **kwargs) self.matches = Match.objects.select_related().filter( Q(opp1=self) | Q(opp2=self) ) On Friday, September 21, 2012 11:15:34 AM UTC+2, Martin J. Laubach wrote: > > My problem is when want to display overall player statistics I get about >> 1200 DB-Queries. >> This is because for each player there will be 5 Queries due to >> matches_won, matches_draw, goals, matches_played functions. >> > > First easy optimisation: keep Match.objects.select_related().filter( > Q(opp1=self)|Q(opp2=self)) in an instance variable and use that QuerySet > as base of all your calculations. Do NOT add additional filters for > matches_draw/matches_won/matches_played, instead iterate over the results > and do the calculations in Python. That way the QuerySet result will be > cached the first time you're iterating over it and won't result in any more > database accesses later on. > >Cheers, > > mjl > > -- You received this message because you are subscribed to the Google Groups "Django users" group. To view this discussion on the web visit https://groups.google.com/d/msg/django-users/-/crAR4nXZyugJ. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
Re: reduce number of DB-Queries
I think aggregation can't be used because the calculations are a bit more complex than just the sum/average/count/min/max of one field. -- You received this message because you are subscribed to the Google Groups "Django users" group. To view this discussion on the web visit https://groups.google.com/d/msg/django-users/-/P2aCE_gXHgYJ. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
