Yes that whats I meant. I was hoping to not have to create a class to do this, but its not going to be take long. I think was just reading more into what column_property could do.
Thanks a lot, Nathan BTW SQLAlchemy is brilliant! 2009/5/18 Michael Bayer <[email protected]>: > > > On May 17, 2009, at 2:13 PM, Nathan Harmston wrote: > >> >> Hmm....I tried it again with echo=debug and some output but it seems >> that my query returns no results (although when I try it on its own it >> returns the right ones). When I try to just use it with only one name >> in bar_table, it works perfectly but with multiple names it doesnt >> work. Can column_property handle this kind of thing? or should I be >> looking for something else? > > do you mean multiple rows in bar_table with the same foo_id? no, that > wont work, your database should actually be raising an error. For a > one-to-many relationship you'll have to map bar_table in a class and > link to it via relation(). > > > > >> >> >> foo_table = Table("foo", metadata, >> Column("foo_id", Integer, primary_key=True), >> Column("data", Text, nullable=False) >> ) >> >> bar_table = Table("bar", metadata, >> Column("bar_id", Integer, primary_key=True), >> Column("foo_id", Integer, ForeignKey("foo.foo_id")), >> Column("name", Text) >> ) >> >> class Foo(object): >> pass >> >> mapper(Foo, foo_table, properties={ "names" : >> column_property(select([bar_table.c.name], foo_table.c.foo_id == >> bar_table.c.foo_id).label("names")) } ) >> >> session = Session() >> for i in session.query(Foo): >> print i, i.names >> >> 2009-05-17 19:06:42,031 INFO sqlalchemy.engine.base.Engine.0x...2e70 >> BEGIN >> 2009-05-17 19:06:42,033 INFO sqlalchemy.engine.base.Engine.0x...2e70 >> SELECT (SELECT bar.name >> FROM bar >> WHERE foo.foo_id = bar.foo_id) AS names, foo.foo_id AS foo_foo_id, >> foo.data AS foo_data >> FROM foo >> 2009-05-17 19:06:42,033 INFO sqlalchemy.engine.base.Engine.0x...2e70 >> [] >> >> Many thanks again, >> >> Nathan >> >> >> 2009/5/15 Michael Bayer <[email protected]>: >>> >>> put echo='debug' to see what the results are. >>> >>> On May 15, 2009, at 8:08 AM, Nathan Harmston wrote: >>> >>>> >>>> Hi, >>>> >>>> I am trying to use a column property, I have a class Foo: >>>> >>>> class Foo(object): >>>> pass >>>> >>>> which is mapped to a table foo_table and it has multiple names which >>>> are stored in bar_table with foo_id as the primary key in Foo and >>>> ForeignKey in bar_table. >>>> >>>> I am trying to use a column property to access these: >>>> >>>> mapper(Foo, foo_table, properties={ "names" : >>>> column_property(select([bar_table.c.name], bar_table.c.foo_id == >>>> foo_table.c.foo_id).label("names")) } ) >>>> >>>> so when I try to query any of the objects I just get no results >>>> back, >>>> but when I try it without the column property I get something back. >>>> >>>> Am I just doing something incredibly stupid wrong? >>>> >>>> Many thanks in advance, >>>> >>>> Nathan >>>> >>>>> >>> >>> >>>> >>> >> >> > > > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
