Thanks Conor! The callable works like a charm! It's great news! (I've been trying to figure out this for 3 days... yeah... I guess I'm not that smart)
Now that I have it working, a “design” question pops up. Nothing technical, really. As Connor mentioned in his reply: “Usually we consider the table with the foreign key as the "child" table, but that's just being picky” That's very true, and now I don't know how to design it... I can do it the way I asked or... class Child(rdb.Model): rdb.metadata(metadata) rdb.tablename("children_table") id = Column("id", Integer, primary_key=True) parent_id = Column("id", Integer, ForeignKey(“parent_table.id”)) # New! type = Column("type", ShortInteger) # New! field1 = Column(“field1”, String(64)) #Irrelevant def __init__(self): self.field1 = “hello world” class Parent(rdb.Model): rdb.metadata(metadata) rdb.tablename("parent_table”) id = Column("id", Integer, primary_key=True) child1 = relationship( # Well... this I still don't know how to write it down, # but it would be something like: # Give me all the children whose “parent_id” is my “id” # AND type == 1 # I'll deal with the joins and that depending on your answer, guys ) child2 = relationship( # Would be same as above # AND type == 2 ) This may be good for adding new children to the parent class... If I add a “Parent.child3”, I just need to create a new relationship very similar to the already existing ones. The way I asked in my former question would imply creating a new relationship AND adding a new foreign key to the parent. I'd like to know what people that know much more about databases think :) 2010/10/29 Conor <conor.edward.da...@gmail.com>: > On 10/29/2010 09:43 AM, Hector Blanco wrote: > > Hello list... > > I wrote a couple of days ago about how to model an structure of three > classes > (http://groups.google.com/group/sqlalchemy/browse_thread/thread/5ba5c4ad16f789d6#). > I thing I almost have it, but I am still getting problems mapping an > structure like this. > > class Child(rdb.Model): > def __init__(self): > self.field1 = “hello world” > > class Parent(rdb.Model): > def __init__(self): > self.child1 = Child() > self.child2 = Child() > > The “Parent” class has two different instances of a Child() class. I > am not even sure about how to treat this (two different 1:1 > relationships or a 1:2 relationship). > > My last try is this mapping: > > (don't let the rdb.Model thing fool you, is just something that > automatically maps the class to the table specified in rdb.tablename). > > class Child(rdb.Model): > rdb.metadata(metadata) > rdb.tablename("children_table") > id = Column("id", Integer, primary_key=True) > field1 = Column(“field1”, String(64)) #Irrelevant > def __init__(self): > self.field1 = “hello world” > > class Parent(rdb.Model): > rdb.metadata(metadata) > rdb.tablename("parent_table”) > > id = Column("id", Integer, primary_key=True) > child1_id = Column("child_1_id", Integer, > ForeignKey("children_table.id")) > child2_id = Column("child_2_id", Integer, > ForeignKey("children_table.id")) > > child1 = relationship(Child, > primaryjoin = (child1_id=="children_table.id") > ) > > child2 = relationship(Child, > primaryjoin = (child2_id=="children_table.id") > ) > > I have tried (almost) everything. I say “almost” because obviously I > haven't tried the right thing. I keep getting errors that sqlalchemy > can't determine the relationship between the two tables. > > It looks very similar to: > http://www.sqlalchemy.org/docs/orm/relationships.html#multiple-relationships-against-the-same-parent-child > > but I also understand that maybe I can't get this working that way, > because I am actually putting two ForeignKeys from the same table > (“children_table”) in the parent table. I am not sure how that will > work out, or is correct or what... :-( > > Thank you! > > Usually we consider the table with the foreign key as the "child" table, but > that's just being picky. The problem is that SQLAlchemy is treating > "children_table.id" as a literal instead of a clause, so your join would be > like (parent JOIN child ON parent.child1_id = 'children_table.id'). > Obviously that is not what you want. There are several ways to formulate > primaryjoin/secondaryjoin. > > Pass the whole thing in as a string: > > child1 = relationship(Child, primaryjoin="Parent.child1_id == Child.id") > > Use the column objects directly (this requires that Child be defined before > Parent): > > child1 = relationship(Child, primaryjoin=child1_id==Child.id) > > Use a callable (my favorite): > > child1 = relationship(Child, primaryjoin=lambda: Parent.child1_id == > Child.id) > > -Conor > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.