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 [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
-~----------~----~----~----~------~----~------~--~---
