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