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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
