> 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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
