you must specify explicit join conditions on your relations as in: http://www.sqlalchemy.org/docs/adv_datamapping.myt#adv_datamapping_relations_customjoin
and the example similar to what youre doing is: http://www.sqlalchemy.org/docs/adv_datamapping.myt#adv_datamapping_relations_multiplejoin alanp wrote: > Hi all, > > Excuse the long post: I hope the extra info (code, SA logging info, db > queries) helps illustrate my problem. > > BTW I'm using SQLAlchemy 0.1.4). BTW2: I haven't examined the SA code > yet to see what I'm doing wrong: I'm hoping some-one can point out my > stupidity before I spend too much time poring over the SA code :-) > > I have a table with two columns referencing different rows of another > table. However the same FK is being inserted into both columns. I've > tried using eager loading and adding 'use_alias' to the relation() but > it doesn't help. > > So where am I being an idiot? > > Thanks > Alan > > The promised extra info... > > *) SA logging output (table create): > [2006-03-28 17:34:03,641] [engine]: > CREATE TABLE arp_images( > image_id SERIAL NOT NULL PRIMARY KEY, > file_name TEXT > ) > [2006-03-28 17:34:03,641] [engine]: None > [2006-03-28 17:34:03,816] [engine]: > CREATE TABLE arp_others( > other_id SERIAL NOT NULL PRIMARY KEY, > image1_id INTEGER REFERENCES arp_images(image_id), > image2_id INTEGER REFERENCES arp_images(image_id), > name TEXT > ) > [2006-03-28 17:34:03,816] [engine]: None > > > *) SA logging (insert): > [2006-03-28 17:34:03,970] [engine]: select > nextval('arp_images_image_id_seq') > [2006-03-28 17:34:03,971] [engine]: None > [2006-03-28 17:34:03,972] [engine]: INSERT INTO arp_images (image_id, > file_name) VALUES (%(image_id)s, %(file_name)s) > [2006-03-28 17:34:03,972] [engine]: {'file_name': 'image-1', > 'image_id': 1L} > [2006-03-28 17:34:03,974] [engine]: select > nextval('arp_images_image_id_seq') > [2006-03-28 17:34:03,975] [engine]: None > [2006-03-28 17:34:03,975] [engine]: INSERT INTO arp_images (image_id, > file_name) VALUES (%(image_id)s, %(file_name)s) > [2006-03-28 17:34:03,976] [engine]: {'file_name': 'image-2', > 'image_id': 2L} > [2006-03-28 17:34:03,978] [engine]: select > nextval('arp_others_other_id_seq') > [2006-03-28 17:34:03,978] [engine]: None > [2006-03-28 17:34:03,979] [engine]: INSERT INTO arp_others (other_id, > image1_id, image2_id, name) VALUES (%(other_id)s, %(image1_id)s, > %(image2_id)s, %(name)s) > [2006-03-28 17:34:03,979] [engine]: {'image2_id': 1L, 'name': 'other', > 'other_id': 1L, 'image1_id': 1L} > > > *) Db contents > psql -U test -d asmpipe_dev -c "select * from arp_images" > image_id | file_name > ----------+----------- > 1 | image-1 > 2 | image-2 > (2 rows) > > psql -U test -d asmpipe_dev -c "select * from arp_others" > other_id | image1_id | image2_id | name > ----------+-----------+-----------+------- > 1 | 1 | 1 | other > (1 row) > > image2_id should be 2. > > > *) SA logging & output of test prog: > [2006-03-28 17:54:03,717] [engine]: SELECT arp_others.image2_id AS > arp_others_image2_id, arp_others.name AS arp_others_name, > arp_others.image1_id AS arp_others_image1_id, arp_others.other_id AS > arp_others_other_id > FROM arp_others > WHERE arp_others.name = %(arp_others_name)s > [2006-03-28 17:54:03,717] [engine]: {'arp_others_name': 'other'} > 1 [2006-03-28 17:54:03,721] [engine]: SELECT arp_images.image_id AS > arp_images_image_id, arp_images.file_name AS arp_images_file_name > FROM arp_images > WHERE arp_images.image_id = %(arp_images_image_id)s AND > arp_images.image_id = %(arp_images_image_id_1)s > [2006-03-28 17:54:03,721] [engine]: {'arp_images_image_id': 1, > 'arp_images_image_id_1': None} > [2006-03-28 17:54:03,723] [engine]: SELECT arp_images.image_id AS > arp_images_image_id, arp_images.file_name AS arp_images_file_name > FROM arp_images > WHERE arp_images.image_id = %(arp_images_image_id)s AND > arp_images.image_id = %(arp_images_image_id_1)s > [2006-03-28 17:54:03,723] [engine]: {'arp_images_image_id': 1, > 'arp_images_image_id_1': None} > [Other(name="'other'", image1='None', image2='None')] > Other(name="'other'", image1='None', image2='None') > None > None > > > *) Offending test prog > from sqlalchemy import * > > engine = create_engine("postgres", > {"database": "asmpipe_dev", > "host": "localhost", > "user": "test", > "password": "test"}, > echo=True, echo_uow=True) > > images = Table("arp_images", engine, > Column("image_id", Integer, primary_key=True), > Column("file_name", String)) > > others = Table("arp_others", engine, > Column("other_id", Integer, primary_key=True), > Column("image1_id", Integer, > ForeignKey("arp_images.image_id")), > Column("image2_id", Integer, > ForeignKey("arp_images.image_id")), > Column("name", String)) > > class Image(object): > def __init__(self, file_name=None): > self.file_name = file_name > > def __repr__(self): > return "%s(file_name=%r)" % (self.__class__.__name__, > repr(self.file_name)) > > class Other(object): > def __init__(self, name=None, image1=None, image2=None): > self.name = name > self.image1 = image1 > self.image2 = image2 > > def __repr__(self): > return "%s(name=%r, image1=%r, image2=%r)" % > (self.__class__.__name__, repr(self.name), repr(self.image1), > repr(self.image2)) > > Image.mapper = mapper(Image, images) > > Other.mapper = mapper(Other, others, > properties={ > "image1": relation(Image.mapper,uselist=False), #use_alias=True, > lazy=False), > "image2": relation(Image.mapper,uselist=False), #use_alias=True, > lazy=False), > }) > > if __name__ == "__main__": > # Drop tables > for t in (others, images): > try: > t.drop() > except SQLError, e: > pass > > # Make tables > images.create() > others.create() > > # Populate tables > i1 = Image(file_name="image-1") > i2 = Image(file_name="image-2") > o = Other(name="other", image1=i1, image2=i2) > objectstore.commit() > > # Query tables > objectstore.clear() > other_rs = Other.mapper.select_by(name="other") > print len(other_rs), other_rs > other = other_rs[0] > print other > print other.image1 > print other.image2 > > > > ------------------------------------------------------- > This SF.Net email is sponsored by xPML, a groundbreaking scripting > language > that extends applications into web and mobile media. Attend the live > webcast > and join the prime developer group breaking into this new coding > territory! > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642 > _______________________________________________ > Sqlalchemy-users mailing list > Sqlalchemy-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users > ------------------------------------------------------- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642 _______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
