[sqlalchemy] Join on a non-normalized model

[Damien]

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