On Jan 12, 2011, at 7:47 AM, Franck wrote:
> Dear all,
>
> I've been trying to answer my own question and use the
> attribute_mapped_collection component like this :
>
> mapper(Tournament, tournaments_table, properties={
> "subscriptions": relationship(Subscription,
> order_by=[desc(Subscription.status), Subscription.user_id],
> collection_class=attribute_mapped_collection('user'))
> })
>
> Now, the following code works :
>
> def subscribe(self, user, status):
>
> current_subscription = self.subscriptions.get(user)
>
> if current_subscription :
> current_subscription.status = status
> else :
> self.subscriptions[user] = Subscription(user, status)
>
> 1. Is it good ? My tables are small, maybe I don't need such logic in the
> mapping ?
SQLA doesn't really implement "INSERT or UPDATE" logic itself since this is
something better left to application design, so what you have here is entirely
reasonable. There's another "INSERT or UPDATE" recipe at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject you can consider.
> 2. The key is a "user" object (not an id), so more DB SQL will be issued,
> right ?
There are more statements emitted when "subscriptions" first loads, assuming
its related "user" object is not already in the session - it would emit a
SELECT per unique .user. if you wanted to manage that you could use eager
loading, or pre-load all user objects ahead of time. It depends on your usage
if you were to load all the .user objects in any case when you happen to access
".subscriptions" and it doesn't make any difference.
> 3. Now, with the subscriptions mapped as a dictionary, the order_by clause
> does not seem to work, which makes sense (subscriptions.values() is not
> ordered). How could I force the order_by nevertheless ?
Using insert-order dictionaries is one way. With a situation like the above,
since the collection is loaded into memory anyway I'd add a @property called
"ordered_subscriptions" that does the ordering in Python. If "subscriptions"
is very large, I'd do this all differently and probably not rely upon the
"subscriptions" collection to manage users, instead calling upon a User, its
collection of "subscriptions" in the other direction, then adding a new
Subscription with the related Tournament, or setting its tournament_id.
Basically get the thing to work first, then see how its normally used to decide
the best approach, if the overhead of relationship() loads is worth it or not.
>
> Thanks a lot !
> Franck
>
>
> On Mon, Jan 10, 2011 at 7:05 PM, Franck <franck.pe...@gmail.com> wrote:
> Dear all,
>
> I'm developing a website aimed at handling tournaments' results and
> subscriptions.
> One subscription is bound to one user and one tournament.
>
> subscriptions_table = Table('SUBSCRIPTIONS', metadata,
> ...
> Column('tournament_id', Integer,
> ForeignKey('TOURNAMENTS.id')),
> Column('user_id', Integer, ForeignKey('USERS.id')),
>
> Here are the mappers (I'm not sure I actually need the backref) :
>
> mapper(Tournament, tournaments_table, properties={
> "subscriptions": relationship(Subscription, backref="tournament")
> })
>
> mapper(Subscription, subscriptions_table, properties={
> "user": relationship(User)
> })
>
> When the user clicks "Subscribe" on the page, the model is checking if the
> user already subscribed to the current tournament or not. If yes,
> SUBSCRIPTIONS.UPDATE should be issued, otherwise SUBSCRIPTIONS.INSERT
>
> As you can see, it's a very classical scenario.
>
> Here's how I implemented the "subscribe" method... but I don't like it at all
> :
>
> class Tournament(Base):
>
> def subscribe(self, user, status):
>
> # Works, but dirty ! Why should I manually query RESULTS since I have
> access to self.subscriptions ?
> # Should I manually look the user in [subscription .user for
> subscription in self.subscriptions] ?
> current_subscription = orm.query(Result).filter(Result.tournament ==
> self).filter(Result.user == user).first()
>
> if current_subscription :
> current_subscription.status = status
> else :
> self.subscriptions.append(Subscription(user, status))
>
> # Commit / rollback logic
>
>
> What do you think ? Since self.subscriptions is already bound, how should I
> properly filter it by user ?
>
> Thanks very much for your help !
> Franck
>
>
>
>
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
