I don't see anything wrong with your model and no it shouldn't matter that watersheds.name is not a primary key. expand upon the meaning of "not effective" here.
Damien wrote: > > Hello list, > > I need some advice on setting a one-to-many join in my particular case > (non-normalized db model) > > > I have the following situation: > - 2 tables with a 1..n join - note: the references table field is not > a primary key (I have to work on a non-normalized model, that's life) > - I need to setup a 1..n in sa > - I used the many-to-one relationship exmaple to get inspired: > http://www.sqlalchemy.org/docs/05/mappers.html#many-to-one > > > My problem is: > - the join is not effective > - I wonder if it is due to the fact that my referenced table column is > not a primary key? > > > Here is my model: > from pylons import config > > from sqlalchemy import Column, MetaData, Table, types > from sqlalchemy.orm import mapper, relation > from sqlalchemy.schema import ForeignKey > > from mapfish.sqlalchemygeom import Geometry > from mapfish.sqlalchemygeom import GeometryTableMixIn > > #from edric_hydro.model.checkpoints import Checkpoint > > > watersheds_table = Table( > 'bv_hydrosuisse', > MetaData(config['pylons.g'].sa_edric_hydro_engine), > Column('the_geom', Geometry(900913)), > Column('name', types.String(50)), > autoload=True) > > class Watershed(GeometryTableMixIn): > # for GeometryTableMixIn to do its job the __table__ property > # must be set here > __table__ = watersheds_table > > > checkpoints_table = Table( > 'jonctions', > MetaData(config['pylons.g'].sa_edric_hydro_engine), > Column('the_geom', Geometry(900913)), > Column('name', types.String(50), ForeignKey > (watersheds_table.c.name)), # maybe watershed.id > autoload=True) > > class Checkpoint(GeometryTableMixIn): > # for GeometryTableMixIn to do its job the __table__ property > # must be set here > __table__ = checkpoints_table > > mapper(Checkpoint, checkpoints_table) > > > mapper(Watershed, watersheds_table, properties={ > #'checkpoint': relation(Checkpoint) > 'checkpoint': relation(Checkpoint, backref='bv_hydrosuissedsads') > }) > > > from pylons import config > > from sqlalchemy import MetaData > from sqlalchemy.orm import scoped_session, sessionmaker > > Session = scoped_session(sessionmaker()) > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---