Re: Django newbie: lost in custom managers
> cursor.execute("""
> SELECT sports_players.city_id, sports_players.team_id,
> count(sports_players.team_id) FROM sports_players,
> sports_mastercitylist
> WHERE sports_players.city_id=sports_mastercitylist.id
> AND sports_mastercitylist.cityslug = %s
> GROUP BY 1, 2
> ORDER BY 1""" % city)
One of the common ways of doing this is described at
http://www.djangoproject.com/documentation/db-api/#extra-select-none-where-none-params-none-tables-none
where you can use the extra() call of a model to tack on an extra
bit in your select clause. Thus, you might use something like
(adjusting for conventions of class-names being capitalized)
items = MasterTeamList.objects.select_related().extra(
select={
'num_players' : """
SELECT Count(*)
FROM sports_players sp
WHERE sp.team = sports_masterteamlist.id
"""})
This automatically returns your list of teams, but they each have
an extra attribute of "num_players" to give the number of players
on that team.
Theoretically, you might even be able to order_by('num_players')
if there was reason to. This might obviate the need for the
PlayerCounts class, especially as you already seem to have this
information tracked elsewhere (the Players have a "team"
attribute linking them, so there's not much need to duplicate this).
Things might get a bit hairier if it was a M2M relationship (one
player can play on more than one team), but then it just involves
bringing in the linking table.
Since you have a query object-set returned from this, you can
then do things like
city = items.get(pk=city_id)
One other small note...if you stick with your existing scheme,
you may have a SQL injection vulnerability with your SQL, as you
don't use the standard DB quoting mechanism. Instead of
execute("""SELECT ... %s ...""" % city)
you should use
execute("""SELECT ... ? ...""", (city, ))
and let the execute() call take the second parameter (a tuple of
values to replace) and use a "?" as the placeholder.
I don't know if my top suggestion will solve your problem, but
that's at least how I'd do what I understand you're trying to do.
And it keeps you a bit closer to the Django ORM which has some
added benefits and understandability.
-tim
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---
Re: Django newbie: lost in custom managers
[EMAIL PROTECTED] wrote:
> cursor.execute("""
> SELECT sports_players.city_id, sports_players.team_id,
> count(sports_players.team_id) FROM sports_players,
> sports_mastercitylist
> WHERE sports_players.city_id=sports_mastercitylist.id
> AND sports_mastercitylist.cityslug = %s
> GROUP BY 1, 2
> ORDER BY 1""" % city)
try changing that last part to "Order by 1 """, [city])
http://www.djangoproject.com/documentation/model-api/#executing-custom-sql
--B
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---
Re: Django newbie: lost in custom managers
[EMAIL PROTECTED] wrote: > Mr. Fordham, if you are ever within 500 miles of St. Petersburg > Florida, I owe you beer or dinner or my firstborn or something. That > tiny little change is all I needed. It works. Thank you so much for > your help. > I actually live w/in 500 miles (Savannah, Ga) but saying "thanks" is enough 8) --B --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: Django newbie: lost in custom managers
Mr. Fordham, if you are ever within 500 miles of St. Petersburg
Florida, I owe you beer or dinner or my firstborn or something. That
tiny little change is all I needed. It works. Thank you so much for
your help.
Matt
On Apr 24, 12:15 pm, "Bryan L. Fordham" <[EMAIL PROTECTED]>
wrote:
> [EMAIL PROTECTED] wrote:
> > cursor.execute("""
> > SELECT sports_players.city_id, sports_players.team_id,
> > count(sports_players.team_id) FROM sports_players,
> > sports_mastercitylist
> > WHERE sports_players.city_id=sports_mastercitylist.id
> > AND sports_mastercitylist.cityslug = %s
> > GROUP BY 1, 2
> > ORDER BY 1""" % city)
>
> try changing that last part to "Order by 1 """, [city])
>
> http://www.djangoproject.com/documentation/model-api/#executing-custo...
>
> --B
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---
Django newbie: lost in custom managers
Just when I was getting comfortable with simple views and templates,
I've walked into a wall on my first attempt to do something more
complicated. Any help anyone can give me will have my undying respect.
Let's say my application is a site for youth sports. I have a table of
cities, teams and players. I've written a simple view that gives a
user a list of cities they can see team and player information for.
What I want to have happen when the users clicks the city is for them
to be taken to a page that lists each team in that city with a count
of the number of players on each team. This is a simple group and
count in SQL. So I've created a manager that is pretty much a http://www.djangoproject.com/documentation/model-api/#custom-
managers">cut and paste job from the docs.
The models and the custom manager I have look like this:
class mastercitylist(models.Model):
city = models.CharField(blank=True, maxlength=100)
cityslug = models.SlugField(prepopulate_from=("city",))
class Admin:
pass
def get_absolute_url(self):
return "http://127.0.0.1:8000/data/sports/cities/%s/; %
self.cityslug
def __str__(self):
return self.city
class masterteamlist(models.Model):
name = models.CharField(maxlength=150)
name_slug = models.SlugField(prepopulate_from=('name',))
class Admin:
pass
def __str__(self):
return self.name
class players(models.Model):
...
city = models.ForeignKey(mastercitylist)
team = models.ForeignKey(masterteamlist)
class Admin:
pass
class PlayerManager(models.Manager):
def with_counts(self, city):
from django.db import connection
cursor = connection.cursor()
cursor.execute("""
SELECT sports_players.city_id, sports_players.team_id,
count(sports_players.team_id) FROM sports_players,
sports_mastercitylist
WHERE sports_players.city_id=sports_mastercitylist.id
AND sports_mastercitylist.cityslug = %s
GROUP BY 1, 2
ORDER BY 1""" % city)
result_list = []
for row in cursor.fetchall():
counts = self.model(city_id=row[0], team_id=row[1])
counts.num_responses = row[2]
result_list.append(counts)
return result_list
class PlayerCounts(models.Model):
team = models.ForeignKey(masterteamlist)
city = models.ForeignKey(mastercitylist)
objects = PlayerManager()
Here's the line from my urls.py specific to this problem:
(r'^data/sports/cities/(?P\w*)/$',
'data.sports.views.teamcountview'),
And here's the view:
def teamcountview(request, city):
teamcountlist = PlayerCounts.objects.with_counts(city)
return render_to_response('sports/teamcountview.html',
{'teamcountlist': teamcountlist})
Now, when I put this all together and go to data/sports/cities/
clearwater I get IndexError: list index out of range. Now, if I take
the SQL I wrote and run it in MySQL with a valid cityslug in the where
clause, it works. I get a list of teams in that city and a count of
the players on those teams. Which leads me to believe that where this
is going wrong is that I'm not passing the cityslug context from the
url to the custom manager correctly, and I'm getting nothing back. But
I have no idea where I've gone wrong, or how to fix it. I've blindly
stumbled to get to this point.
If you see where I've gone wrong, or can tell me a different/easier
way than I've cobbled together, I would greatly appreciate it.
Matt
Matthew Waite
www.mattwaite.com
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---
