Hi,
Thanks for the pointer. I must have read over that 100 times and
missed the contains part. I've managed to put this together:
games =
(session.query(models.Game).options(
sqlalchemy.orm.eagerload('scores')).filter(
models.Game.scores.any(models.Score.player == player)).all())
Unfortunately, with ~6000 games, where the specified user has played
~4000 of them, it takes roughly 45 seconds to issue that query:
time curl "http://localhost:8080/chart/win_average?
width=400&height=400&username=solongordon&playername=Solon&version=2"
Solon played 4195 games.
real 0m44.656s
user 0m0.008s
sys 0m0.008s
As you can see I've attempted to eagerload the scores but that doesn't
seem to help much. I tried to eagerload the players as well but I
can't get that syntax to work. I tried specifying lazy = False in my
Columns in the models, but I don't know if that takes effect after the
database has already been created.
Does anyone have advice for speeding this query up?
Thanks!
gabe
On Sep 21, 4:38 pm, [EMAIL PROTECTED] wrote:
> see
> thesehttp://www.sqlalchemy.org/docs/05/ormtutorial.html#datamapping_joins_...
>
> On Sunday 21 September 2008 20:04:02 Gabe wrote:
>
> > Hi Everyone,
> > I'm somewhat new to database design, so if I've set things up in an
> > incompatible or silly manner please let me know. I have the
> > following tables:
>
> > class Player(Base):
> > __tablename__ = "players"
> > # Stuff about a player.
>
> > class
> > Game(Base):
> > """A class that holds information about a single
> > game."""
>
> > __tablename__ =
> > "games"
>
> > #
> > Columns
> > id = Column(Integer, primary_key =
> > True)
> > board =
> > Column(Binary)
> > match_length =
> > Column(Float)
> > date_added = Column(DateTime, default =
> > datetime.datetime.now)
>
> > class
> > Score(Base):
> > """A class that holds a player's score for a
> > game."""
>
> > __tablename__ =
> > "scores"
>
> > #
> > Columns:
> > id = Column(Integer, primary_key =
> > True)
> > game_id = Column(Integer,
> > ForeignKey("games.id"))
> > player_id = Column(Integer,
> > ForeignKey("players.id"))
> > score =
> > Column(Integer)
>
> > #
> > Relations:
> > game = relation(Game, backref =
> > backref("scores"))
> > player =
> > relation(Player)
>
> > # Back
> > References:
> > # "game" which points to the game for this object.
>
> > I'd like to figure out how to count the wins and losses for a
> > player. I was thinking something along these lines:
> > player = session.query(Player).filter(Player.name ==
> > playername).one() games_containing_player =
> > session.query(Game).filter(player in Game.scores.player).all()
>
> > But that pretty obviously doesn't work. Should I have designed
> > this set up differently? Right now the only thing I've come up
> > with, (which seems to think the player is in every game as well
> > which is wrong) and is really really slow, is:
>
> > Get the player.
> > player = session.query(models.Player).filter(sqlalchemy.and_(
> > models.Player.owner == user,
> > models.Player.name == playername,
> > models.Player.version == version)).one()
> > if not player:
> > return "Not a valid player."
>
> > # Compute the player's win/loss list.
> > win_loss = []
> > games = session.query(models.Game).all()
>
> > def had_player(game):
> > for score in game.scores:
> > if score.player == player:
> > return True
> > return False
>
> > filter(had_player, games)
> > return "%s played %d games." % (player.name, len(games))
>
> > Thanks for the advice,
> > Gabe
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
