Hey there, I have already posted this on stackoverflow<http://stackoverflow.com/questions/18304212/how-do-i-create-a-one-to-many-relationship-with-a-default-one-to-one-property-fo>but not recieved an answer yet so I thought I might try here. To quote from my original question:
Suppose we have these classes: > > class Item(Base): > id = Column(Integer, primary_key=True) > data = Column(String) > i18ns = relationship("ItemI18n", backref="item") > > class ItemI18n(Base): > lang_short = Column(String, primary_key=True) > item_id = Column(Integer, ForeignKey('item.id'), primary_key=True) > name = Column(String) > [on pastebin <http://pastebin.com/LQAMVy0Y>] > The idea here is to have this item's name in multiple languages, for > example in English and German. This works fine so far, one can easily work > with that. However, most times, I am not interested in all (i.e. both) > names but only the users locale. > > For example, if the user is English and wants to have the name in his > language, I see two options: > > # Use it as a list > item = session.query(Item).first() > print item.data, [i18n.name for i18n in item.i18ns if i18n.lang_short > == "en"][0] > > # Get the name separately > item, name = session.query(Item, > ItemI18N.name).join(ItemI18N).filter(ItemI18N.lang_short == "en").first() > print item.data, name > [on pastebin <http://pastebin.com/VfuHBd86>] > > The first one filters the list, the second one queries the language > separately. The second is the more efficient way as it only pulls the data > really needed. However, there is a drawback: I now have to carry around two > variables: `item` and `name`. If I were to extend my `ItemI18N` for > example, add a `description` property, then I would query for `ItemI18N` > and carry those around. > > But business logic is different: I would expect to have an `Item` with a > `name` and `description` attribute, so that I would do something like this: > > item = session.query(Item).first() > print item.data, item.name > > So that's where I want to go: Pull all those attributes from `Item18N` > directly into `Item`. And of course, I would have to specify the language > anywhere. However, I cannot find any recipes for this since I don't even > know what to search for. Can SQLAlchemy do such a thing? > > I also created a complete example <http://pastebin.com/Ea1Bqzcw> for > everything I described (except of course the part I don't know how to > realize). > > *Edit*: I have played around a bit more to see whether I can come up with > a better solution and so far, I have found one way that works. I initially > tried to realize it with `Query.get` but this doesn't work beyond my simple > example, because reality is different. To explain, I have to extend my > initial model by adding a `Language` table and turn `ItemI18N` into a > many-to-many relationship with the primary key being `(lang_id, item_id)`: > > class ItemI18N(Base): > lang_id = Column(Integer, ForeignKey('language.id'), > primary_key=True) > item_id = Column(Integer, ForeignKey('item.id'), primary_key=True) > name = Column(String) > language = relationship("Language", lazy="joined") > > > class Language(Base): > id = Column(Integer, primary_key=True) > short = Column(String) > > Now to get my correct locale I simply turn all loadings into joined > loadings by applying `lazy="joined"` to the complete path. This will > inevitable pull in all languages thus returning more data than I need. My > approach is then completely independent of SQLAlchemy: > > class Item(Base): > ... > i18ns = relationship("ItemI18N", backref="item", cascade="all, > delete-orphan", lazy="joined") > > def name(self, locale): > for i18n in self.i18ns: > if i18n.language.short == locale: > return i18n.name > > But this is not a pretty solution, both because of the overhead of > retrieving *all* I18N data from the database and then fitering that > result back to *one* thus making it completely irrelevant that I pulled > all in the first place (since the locale will stay the same the whole > time). My new full example <http://pastebin.com/iSTkESjg> shows how only > one query is executed and gives my transparent access - but with an ugly > overhead I would like to avoid. > > The example also contains some playing around with > transformations<http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/hybrid.html#building-transformers>I > have done. This could point to a solution from that direction, but I > wasn't happy with this either because it required me to pass in the > `with_transformation` part every time. I'd like it much better if this > would automatically be applied when `Item` is queried. But I have found no > event or other for this. > > So now I have multiple solution attempts that all lack the ease of direct > access compared to the business logic described above. I hope someone is > able to figure out how to close these gaps to produce something nice and > clean. > By the way, at this point there has to come up the question where to provide the locale. Since this is in a web context, the most sensible way in this case is to set it at the session level as this describes the scope. I don't know if events would make sense here. And, adding to that, there came another idea for me: One of the problems is, that the property for the `i18n` would never be included. However, what would be included is a relationship with eager loading, but without a second condition, i.e. limiting the corresponding language it would return all languages, not just one. It would be a neat trick to evaluate the join condition at runtime. Would that be a possibility? Documentation says it only happens at mapper initialization time. Thanks in advance for any help you can provide on this topic. It would be a great thing if we could find a solution that is as clean as possible as I am surely not the only one with this problem and so one could create a separate package if a good approach exists for that. Regards, Florian (javex) -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.