[sqlalchemy] Re: How to link one table to itself?
It's working properly with that relation: mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, primaryjoin=id==media_group_groups.c.groupA_id, secondaryjoin=id==media_group_groups.c.groupB_id, backref=media_groups) Thanks! On Aug 13, 2:15 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 12, 2010, at 1:08 PM, Alvaro Reinoso wrote: Hello, I'm trying to link one table to itself. I have media groups which can contain more media group. I created a relation many to many: media_group_groups = Table( media_group_groups, metadata, Column(groupA_id, Integer, ForeignKey(media_groups.id)), Column(groupB_id, Integer, ForeignKey(media_groups.id)) ) class MediaGroup(rdb.Model): Represents MediaGroup class. Conteins channels and other media groups rdb.metadata(metadata) rdb.tablename(media_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) parents = Column(parents, String(512)) channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref=media_groups) mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups) I got this error: ArgumentError: Could not determine join condition between parent/ child tables on relationship MediaGroup.mediaGroup. Specify a 'primaryjoin' expression. If this is a many-to-many relationship, 'secondaryjoin' is needed as well. When I create the tables I don't get any error, it's just when I add any element to it. Any idea??? Thanks in advance! because you're having a particularly large amount of difficulty here, I'd very much like to determine what about the documentation or behavior of relationships is continuously leading you down the wrong path - this becuase I myself am not familiar with that many ways to get an error without eventually hitting upon the right solution. Ive taken the effort to reconstruct every table and relationship you've expressed in this thread. Below you will see an example of everything you've expressed, pretty much using code snippets you've already illustrated plus reconstructions, leading into a persistence scenario that inserts into all seven tables. I did not come across any of the issues you describe except the initial one expressing that primary/secondaryjoin is needed for MediaGroup.mediaGroup. Please start by running it as is, against the given sqlite database, so you can see how it works. Then, I need you to determine what about your setup is different than this, which would pinpoint the source of the issue- you do this by blending the code below with your app, changing one part at a time until the difference between emitting the error and working properly is localized. Then, I need to know how to detect the mistake you've been making and to raise a more informative error message. The foreign_keys argument is virtually never needed and at the very least I think I am going to remove the mention of that argument from the message. Let me know what you come up with. from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import * Base = declarative_base() metadata = Base.metadata media_group_groups = Table( media_group_groups, metadata, Column(groupA_id, Integer, ForeignKey(media_groups.id)), Column(groupB_id, Integer, ForeignKey(media_groups.id)) ) user_media_groups = Table( user_media_groups, metadata, Column(user_id, Integer, ForeignKey(users.id)), Column(media_group_id, Integer, ForeignKey(media_groups.id)), ) user_channels = Table( user_channels, metadata, Column(user_id, Integer, ForeignKey(users.id)), Column(channel_id, Integer, ForeignKey(channel.id)) ) media_group_channels = Table( media_group_channels, metadata, Column(media_group_id, Integer, ForeignKey(media_groups.id)), Column(channel_id, Integer, ForeignKey(channel.id)) ) class Channel(Base): __tablename__ = 'channel' id = Column(id, Integer, primary_key=True) titleView = Column(title, String(100)) class MediaGroup(Base): __tablename__ = media_groups id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) parents = Column(parents, String(512)) channels = relationship(Channel, secondary=media_group_channels,
[sqlalchemy] Re: How to link one table to itself?
I've tried many things and I always get similar errors. I'm sure all the foreign keys are OK. Could not determine relationship direction for primaryjoin condition 'users.id = user_channels.user_id', on relationship User.channels. Do the columns in 'foreign_keys' represent only the 'foreign' columns in this join condition ?. This error came up from another table and all the tables are working properly without this relation. Almost all the tables are related to each other. Maybe, this relation affects the rest of them in some way. Any idea of How I can solve this problem? Thanks! On Aug 12, 8:36 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 12, 2010, at 5:43 PM, Alvaro Reinoso wrote: I'm trying that, but I got different error: Could not determine relationship direction for primaryjoin condition 'users.id = :id_1', on relationship User.mediaGroups. Specify the 'foreign_keys' argument to indicate which columns on the relationship are foreign. As I can see, the error is related to the users table which I don't use in this class. However, user table is related to media_groups. This is the user's table: class User(rdb.Model): Represents the user rdb.metadata(metadata) rdb.tablename(users) id = Column(id, Integer, primary_key=True) name = Column(name, String(50)) email = Column(email, String(50)) channels = relationship(Channel, secondary=user_channels, order_by=Channel.titleView, backref=users) mediaGroups = relationship(MediaGroup, secondary=user_media_groups, order_by=MediaGroup.title, backref=users) Do I need to add something else to that table? user_media_groups needs to have a ForeignKey that points to users. Thanks!!! On Aug 12, 5:15 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 12, 2010, at 5:09 PM, Michael Bayer wrote: On Aug 12, 2010, at 4:58 PM, Alvaro Reinoso wrote: I'm still working on the solution. I've found out some stuff in internet. I guess I'm close to, but I haven't got it yet. I'm using this for the relation: mediaGroups = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref='media_groups', foreign_keys = [media_group_groups.groupA_id, media_group_groups.groupB_id], primaryjoin = MediaGroup.id == media_group_groups.groupA_id, secondaryjoin = MediaGroup.id == media_group_groups.groupB_id) I'm playing with the parameters, but I usually get this error: ArgumentError: Could not determine relationship direction for primaryjoin condition 'users.id = :id_1', on relationship User.mediaGroups. Specify the 'foreign_keys' argument to indicate which columns on the relationship are foreign. media_group_groups is not available when primaryjoin is evaluated as a string, nor within foreign_keys which is not necessary here since your meta_group_groups already has ForeignKey objects on it, so use a non-string format for primaryjoin. i will add additional examples to the declarative docs. scratch part of that, tablenames are available in the string eval as long as they're from the same MetaData: mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups, primaryjoin=MediaGroup.id==media_group_groups.c.groupA_id, secondaryjoin=MediaGroup.id==media_group_groups.c.groupB_id also the error for the exampe you have above should be 'Table' object has no attribute 'groupA_id'. Thank you! On Aug 12, 1:08 pm, Alvaro Reinoso alvrein...@gmail.com wrote: Hello, I'm trying to link one table to itself. I have media groups which can contain more media group. I created a relation many to many: media_group_groups = Table( media_group_groups, metadata, Column(groupA_id, Integer, ForeignKey(media_groups.id)), Column(groupB_id, Integer, ForeignKey(media_groups.id)) ) class MediaGroup(rdb.Model): Represents MediaGroup class. Conteins channels and other media groups rdb.metadata(metadata) rdb.tablename(media_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) parents = Column(parents, String(512)) channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref=media_groups) mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups) I got this error: ArgumentError: Could not determine join condition between parent/ child tables on relationship MediaGroup.mediaGroup. Specify a 'primaryjoin' expression. If this is a many-to-many relationship, 'secondaryjoin' is needed as well. When I create the tables I don't get any error, it's just when
[sqlalchemy] Re: How to link one table to itself?
I'm still working on the solution. I've found out some stuff in internet. I guess I'm close to, but I haven't got it yet. I'm using this for the relation: mediaGroups = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref='media_groups', foreign_keys = [media_group_groups.groupA_id, media_group_groups.groupB_id], primaryjoin = MediaGroup.id == media_group_groups.groupA_id, secondaryjoin = MediaGroup.id == media_group_groups.groupB_id) I'm playing with the parameters, but I usually get this error: ArgumentError: Could not determine relationship direction for primaryjoin condition 'users.id = :id_1', on relationship User.mediaGroups. Specify the 'foreign_keys' argument to indicate which columns on the relationship are foreign. Thank you! On Aug 12, 1:08 pm, Alvaro Reinoso alvrein...@gmail.com wrote: Hello, I'm trying to link one table to itself. I have media groups which can contain more media group. I created a relation many to many: media_group_groups = Table( media_group_groups, metadata, Column(groupA_id, Integer, ForeignKey(media_groups.id)), Column(groupB_id, Integer, ForeignKey(media_groups.id)) ) class MediaGroup(rdb.Model): Represents MediaGroup class. Conteins channels and other media groups rdb.metadata(metadata) rdb.tablename(media_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) parents = Column(parents, String(512)) channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref=media_groups) mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups) I got this error: ArgumentError: Could not determine join condition between parent/ child tables on relationship MediaGroup.mediaGroup. Specify a 'primaryjoin' expression. If this is a many-to-many relationship, 'secondaryjoin' is needed as well. When I create the tables I don't get any error, it's just when I add any element to it. Any idea??? Thanks in advance! -- 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.
Re: [sqlalchemy] Re: How to link one table to itself?
On Aug 12, 2010, at 5:09 PM, Michael Bayer wrote: On Aug 12, 2010, at 4:58 PM, Alvaro Reinoso wrote: I'm still working on the solution. I've found out some stuff in internet. I guess I'm close to, but I haven't got it yet. I'm using this for the relation: mediaGroups = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref='media_groups', foreign_keys = [media_group_groups.groupA_id, media_group_groups.groupB_id], primaryjoin = MediaGroup.id == media_group_groups.groupA_id, secondaryjoin = MediaGroup.id == media_group_groups.groupB_id) I'm playing with the parameters, but I usually get this error: ArgumentError: Could not determine relationship direction for primaryjoin condition 'users.id = :id_1', on relationship User.mediaGroups. Specify the 'foreign_keys' argument to indicate which columns on the relationship are foreign. media_group_groups is not available when primaryjoin is evaluated as a string, nor within foreign_keys which is not necessary here since your meta_group_groups already has ForeignKey objects on it, so use a non-string format for primaryjoin. i will add additional examples to the declarative docs. scratch part of that, tablenames are available in the string eval as long as they're from the same MetaData: mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups, primaryjoin=MediaGroup.id==media_group_groups.c.groupA_id, secondaryjoin=MediaGroup.id==media_group_groups.c.groupB_id also the error for the exampe you have above should be 'Table' object has no attribute 'groupA_id'. Thank you! On Aug 12, 1:08 pm, Alvaro Reinoso alvrein...@gmail.com wrote: Hello, I'm trying to link one table to itself. I have media groups which can contain more media group. I created a relation many to many: media_group_groups = Table( media_group_groups, metadata, Column(groupA_id, Integer, ForeignKey(media_groups.id)), Column(groupB_id, Integer, ForeignKey(media_groups.id)) ) class MediaGroup(rdb.Model): Represents MediaGroup class. Conteins channels and other media groups rdb.metadata(metadata) rdb.tablename(media_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) parents = Column(parents, String(512)) channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref=media_groups) mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups) I got this error: ArgumentError: Could not determine join condition between parent/ child tables on relationship MediaGroup.mediaGroup. Specify a 'primaryjoin' expression. If this is a many-to-many relationship, 'secondaryjoin' is needed as well. When I create the tables I don't get any error, it's just when I add any element to it. Any idea??? Thanks in advance! -- 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. -- 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.
[sqlalchemy] Re: How to link one table to itself?
I'm trying that, but I got different error: Could not determine relationship direction for primaryjoin condition 'users.id = :id_1', on relationship User.mediaGroups. Specify the 'foreign_keys' argument to indicate which columns on the relationship are foreign. As I can see, the error is related to the users table which I don't use in this class. However, user table is related to media_groups. This is the user's table: class User(rdb.Model): Represents the user rdb.metadata(metadata) rdb.tablename(users) id = Column(id, Integer, primary_key=True) name = Column(name, String(50)) email = Column(email, String(50)) channels = relationship(Channel, secondary=user_channels, order_by=Channel.titleView, backref=users) mediaGroups = relationship(MediaGroup, secondary=user_media_groups, order_by=MediaGroup.title, backref=users) Do I need to add something else to that table? Thanks!!! On Aug 12, 5:15 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 12, 2010, at 5:09 PM, Michael Bayer wrote: On Aug 12, 2010, at 4:58 PM, Alvaro Reinoso wrote: I'm still working on the solution. I've found out some stuff in internet. I guess I'm close to, but I haven't got it yet. I'm using this for the relation: mediaGroups = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref='media_groups', foreign_keys = [media_group_groups.groupA_id, media_group_groups.groupB_id], primaryjoin = MediaGroup.id == media_group_groups.groupA_id, secondaryjoin = MediaGroup.id == media_group_groups.groupB_id) I'm playing with the parameters, but I usually get this error: ArgumentError: Could not determine relationship direction for primaryjoin condition 'users.id = :id_1', on relationship User.mediaGroups. Specify the 'foreign_keys' argument to indicate which columns on the relationship are foreign. media_group_groups is not available when primaryjoin is evaluated as a string, nor within foreign_keys which is not necessary here since your meta_group_groups already has ForeignKey objects on it, so use a non-string format for primaryjoin. i will add additional examples to the declarative docs. scratch part of that, tablenames are available in the string eval as long as they're from the same MetaData: mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups, primaryjoin=MediaGroup.id==media_group_groups.c.groupA_id, secondaryjoin=MediaGroup.id==media_group_groups.c.groupB_id also the error for the exampe you have above should be 'Table' object has no attribute 'groupA_id'. Thank you! On Aug 12, 1:08 pm, Alvaro Reinoso alvrein...@gmail.com wrote: Hello, I'm trying to link one table to itself. I have media groups which can contain more media group. I created a relation many to many: media_group_groups = Table( media_group_groups, metadata, Column(groupA_id, Integer, ForeignKey(media_groups.id)), Column(groupB_id, Integer, ForeignKey(media_groups.id)) ) class MediaGroup(rdb.Model): Represents MediaGroup class. Conteins channels and other media groups rdb.metadata(metadata) rdb.tablename(media_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) parents = Column(parents, String(512)) channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref=media_groups) mediaGroup = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, backref=media_groups) I got this error: ArgumentError: Could not determine join condition between parent/ child tables on relationship MediaGroup.mediaGroup. Specify a 'primaryjoin' expression. If this is a many-to-many relationship, 'secondaryjoin' is needed as well. When I create the tables I don't get any error, it's just when I add any element to it. Any idea??? Thanks in advance! -- 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 athttp://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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received