Thank you very much ! I'm going to study the different options I have here.
On Wed, Jan 12, 2011 at 3:01 PM, Michael Bayer <[email protected]>wrote: > > 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 <[email protected]> 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 [email protected]. > 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. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]<sqlalchemy%[email protected]> > . > 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 [email protected]. 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.
