Re: [sqlalchemy] self-referential relationship w/ declarative style
On Feb 20, 2011, at 10:12 PM, Ryan wrote: I'm attempting a self-referential mapping on a Client object that includes these two columns: id = Column(Integer, primary_key=True) inviter_id = Column(Integer, ForeignKey('users.id'), nullable=True) Started here with no luck: inviter = relationship('Client', primaryjoin='Client.id == Client.inviter_id', uselist=False) Then read about self-referential mapping in the docs and tried with no luck: inviter = relationship('Client', remote_side='Client.id', uselist=False) And this with an error: relationship('Client', remote_side=[Client.id], uselist=False) Would be a great help to see how this is done in a declarative style. Thanks! the last example in the section http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships illustrates a declarative self-referential relationship. Note that the id Column object can be referenced directly when you're inside the class declaration itself. -- 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. -- 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.
Re: [sqlalchemy] self-referential relationship w/ declarative style
On Feb 22, 2011, at 9:03 PM, Ryan McKillen wrote: Mike, thanks a lot. Big help. I'm almost there. This seems to do the trick: usersid = Column(Integer, primary_key=True, key='id') inviter_id = Column(Integer, ForeignKey('users.id')) inviter = relationship('User', uselist = False, backref = backref('invitee', remote_side=usersid, uselist=True), ) When there are two users, one being the inviter (parent) and the other being the invitee (child), it works like a charm: self.assertEqual(invitee1.inviter.id, inviter.id) self.assertEqual(inviter.invitee[0].id, invitee1.id) But add a third user, one being the inviter and two being the invitees, invitee1.inviter is None. probably because of that uselist=False, which makes it into a one-to-one. Adjacency list is a standard single foreign key relationship - one-to-many on one side, many-to-one on the other. There's an illustration of exactly how the data resides in the table: http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships Any ideas for me? On Tue, Feb 22, 2011 at 7:54 AM, Michael Bayer mike...@zzzcomputing.com wrote: On Feb 20, 2011, at 10:12 PM, Ryan wrote: I'm attempting a self-referential mapping on a Client object that includes these two columns: id = Column(Integer, primary_key=True) inviter_id = Column(Integer, ForeignKey('users.id'), nullable=True) Started here with no luck: inviter = relationship('Client', primaryjoin='Client.id == Client.inviter_id', uselist=False) Then read about self-referential mapping in the docs and tried with no luck: inviter = relationship('Client', remote_side='Client.id', uselist=False) And this with an error: relationship('Client', remote_side=[Client.id], uselist=False) Would be a great help to see how this is done in a declarative style. Thanks! the last example in the section http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships illustrates a declarative self-referential relationship. Note that the id Column object can be referenced directly when you're inside the class declaration itself. -- 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. -- 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. -- 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. -- 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.
Re: [sqlalchemy] self-referential relationship w/ declarative style
I added that in because without it I get: TypeError: Incompatible collection type: User is not list-like On Tue, Feb 22, 2011 at 6:47 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Feb 22, 2011, at 9:03 PM, Ryan McKillen wrote: Mike, thanks a lot. Big help. I'm almost there. This seems to do the trick: usersid = Column(Integer, primary_key=True, key='id') inviter_id = Column(Integer, ForeignKey('users.id')) inviter = relationship('User', uselist = False, backref = backref('invitee', remote_side=usersid, uselist=True), ) When there are two users, one being the inviter (parent) and the other being the invitee (child), it works like a charm: self.assertEqual(invitee1.inviter.id, inviter.id) self.assertEqual(inviter.invitee[0].id, invitee1.id) But add a third user, one being the inviter and two being the invitees, invitee1.inviter is None. probably because of that uselist=False, which makes it into a one-to-one. Adjacency list is a standard single foreign key relationship - one-to-many on one side, many-to-one on the other. There's an illustration of exactly how the data resides in the table: http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships Any ideas for me? On Tue, Feb 22, 2011 at 7:54 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Feb 20, 2011, at 10:12 PM, Ryan wrote: I'm attempting a self-referential mapping on a Client object that includes these two columns: id = Column(Integer, primary_key=True) inviter_id = Column(Integer, ForeignKey('users.id'), nullable=True) Started here with no luck: inviter = relationship('Client', primaryjoin='Client.id == Client.inviter_id', uselist=False) Then read about self-referential mapping in the docs and tried with no luck: inviter = relationship('Client', remote_side='Client.id', uselist=False) And this with an error: relationship('Client', remote_side=[Client.id], uselist=False) Would be a great help to see how this is done in a declarative style. Thanks! the last example in the section http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationshipsillustrates a declarative self-referential relationship. Note that the id Column object can be referenced directly when you're inside the class declaration itself. -- 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. -- 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. -- 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. -- 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. -- 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.
Re: [sqlalchemy] self-referential relationship w/ declarative style
one side scalar, one side collection. the collection side you use .append(). You decide which end is the non-collection by setting remote_side, in your code below its invitee. On Feb 22, 2011, at 9:59 PM, Ryan McKillen wrote: I added that in because without it I get: TypeError: Incompatible collection type: User is not list-like On Tue, Feb 22, 2011 at 6:47 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Feb 22, 2011, at 9:03 PM, Ryan McKillen wrote: Mike, thanks a lot. Big help. I'm almost there. This seems to do the trick: usersid = Column(Integer, primary_key=True, key='id') inviter_id = Column(Integer, ForeignKey('users.id')) inviter = relationship('User', uselist = False, backref = backref('invitee', remote_side=usersid, uselist=True), ) When there are two users, one being the inviter (parent) and the other being the invitee (child), it works like a charm: self.assertEqual(invitee1.inviter.id, inviter.id) self.assertEqual(inviter.invitee[0].id, invitee1.id) But add a third user, one being the inviter and two being the invitees, invitee1.inviter is None. probably because of that uselist=False, which makes it into a one-to-one. Adjacency list is a standard single foreign key relationship - one-to-many on one side, many-to-one on the other. There's an illustration of exactly how the data resides in the table: http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships Any ideas for me? On Tue, Feb 22, 2011 at 7:54 AM, Michael Bayer mike...@zzzcomputing.com wrote: On Feb 20, 2011, at 10:12 PM, Ryan wrote: I'm attempting a self-referential mapping on a Client object that includes these two columns: id = Column(Integer, primary_key=True) inviter_id = Column(Integer, ForeignKey('users.id'), nullable=True) Started here with no luck: inviter = relationship('Client', primaryjoin='Client.id == Client.inviter_id', uselist=False) Then read about self-referential mapping in the docs and tried with no luck: inviter = relationship('Client', remote_side='Client.id', uselist=False) And this with an error: relationship('Client', remote_side=[Client.id], uselist=False) Would be a great help to see how this is done in a declarative style. Thanks! the last example in the section http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships illustrates a declarative self-referential relationship. Note that the id Column object can be referenced directly when you're inside the class declaration itself. -- 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. -- 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. -- 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. -- 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. -- 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. -- 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.
Re: [sqlalchemy] self-referential relationship w/ declarative style
Got it. Many thanks! On Tue, Feb 22, 2011 at 7:02 PM, Michael Bayer mike...@zzzcomputing.comwrote: one side scalar, one side collection. the collection side you use .append(). You decide which end is the non-collection by setting remote_side, in your code below its invitee. On Feb 22, 2011, at 9:59 PM, Ryan McKillen wrote: I added that in because without it I get: TypeError: Incompatible collection type: User is not list-like On Tue, Feb 22, 2011 at 6:47 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Feb 22, 2011, at 9:03 PM, Ryan McKillen wrote: Mike, thanks a lot. Big help. I'm almost there. This seems to do the trick: usersid = Column(Integer, primary_key=True, key='id') inviter_id = Column(Integer, ForeignKey('users.id')) inviter = relationship('User', uselist = False, backref = backref('invitee', remote_side=usersid, uselist=True), ) When there are two users, one being the inviter (parent) and the other being the invitee (child), it works like a charm: self.assertEqual(invitee1.inviter.id, inviter.id) self.assertEqual(inviter.invitee[0].id, invitee1.id) But add a third user, one being the inviter and two being the invitees, invitee1.inviter is None. probably because of that uselist=False, which makes it into a one-to-one. Adjacency list is a standard single foreign key relationship - one-to-many on one side, many-to-one on the other. There's an illustration of exactly how the data resides in the table: http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationships Any ideas for me? On Tue, Feb 22, 2011 at 7:54 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Feb 20, 2011, at 10:12 PM, Ryan wrote: I'm attempting a self-referential mapping on a Client object that includes these two columns: id = Column(Integer, primary_key=True) inviter_id = Column(Integer, ForeignKey('users.id'), nullable=True) Started here with no luck: inviter = relationship('Client', primaryjoin='Client.id == Client.inviter_id', uselist=False) Then read about self-referential mapping in the docs and tried with no luck: inviter = relationship('Client', remote_side='Client.id', uselist=False) And this with an error: relationship('Client', remote_side=[Client.id], uselist=False) Would be a great help to see how this is done in a declarative style. Thanks! the last example in the section http://www.sqlalchemy.org/docs/orm/relationships.html#adjacency-list-relationshipsillustrates a declarative self-referential relationship. Note that the id Column object can be referenced directly when you're inside the class declaration itself. -- 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. -- 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. -- 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. -- 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. -- 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. -- 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. -- 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,
[sqlalchemy] self-referential relationship w/ declarative style
I'm attempting a self-referential mapping on a Client object that includes these two columns: id = Column(Integer, primary_key=True) inviter_id = Column(Integer, ForeignKey('users.id'), nullable=True) Started here with no luck: inviter = relationship('Client', primaryjoin='Client.id == Client.inviter_id', uselist=False) Then read about self-referential mapping in the docs and tried with no luck: inviter = relationship('Client', remote_side='Client.id', uselist=False) And this with an error: relationship('Client', remote_side=[Client.id], uselist=False) Would be a great help to see how this is done in a declarative style. Thanks! -- 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.