Thanks for that, I think I should have made my question a little clearer, (it works for what I was asking but not what I want it to do).
spec_names = join(species_table, species_names_table, and_(species_table.c.rank=="species", species_table.c.taxa_id==species_names_table.c.taxa)) mapper(Species, spec_names, properties={'names':column_property( species_names_table.c.name)}) So far I have done this gives me kind of the correct result, except I get multiple instances of a Species object back because it has multiple names. Instead what I what to do is just create a single instance of the Species object with a list of names in an attribute called "names". How do I accomplish this? Also what do you mean by the correlated criterion of the column_property I have looked for this and have been unable to find it. Many thanks in advance, Nathan 2009/1/26 Michael Bayer <mike...@zzzcomputing.com> > > On Jan 26, 2009, at 12:50 PM, Nathan Harmston wrote: > > Hi, > > I am currently trying to use an SQL expression as a mapped attribute. I > have a table called species_table and a species_names_tables, there is a one > to many relationship between them on species_table.c.taxa_id and > species_names_table.c.taxa. So one species can have multiple names. I am > currently trying to make it so that a Species object has an attribute called > "names" which is a list of the names held in the species_table (there are > other attributes in this table, but I don't want any of them). So this is > what I ve tried to do: > > mapper(Species, select([species_table], > species_table.c.rank=='species').alias('speciesfooalias'), > properties={'names':column_property(select([species_names_table.c.name], > species_table.c.taxa_id==species_names_table.c.taxa).label("names"))}) > > #metadata.create_all(engine) > session = Session() > s =session.query(Species).filter(Species.taxa_id==7).one() > this is the problem, because now it says that > raise exceptions.InvalidRequestError('No rows returned for one()') > sqlalchemy.exceptions.InvalidRequestError: No rows returned for one() > > however if I just do it using a simple mapper with no column_property: > mapper(Species, select([species_table], > species_table.c.rank=='species').alias('speciesfooalias')) > > I get the correct output. I am using SQLAlchemy 0.4.8 > > I am pretty confused by this. Any help is very much appreciated. > > > your column_property() must be expressed in terms of the mapped selectable. > Therefore assign your select([species_table]) to a variable, map to that, > and also reference it in the correlated criterion of your column_property(). > > in this case, since you are mapping to a select anyway, its probably easier > and more efficient to map to a simple join of species_table and > species_names_table, including species_names_table.c.name in the list of > columns so that it is mapped directly, and forego the usage of the separate > column_propery(). > > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~----------~----~----~----~------~----~------~--~---