Hi Mike, I have two models that I did not provide in the example: Entity and Group. Every group can assign some customer attributes to an entity, this is reflected in GroupEntityAttributes. Let's say GroupEntityAttributes(group_id=1, entity_id=1, key="color", value="red") would mean that Group 1 assigned a customer attribute "color" with a value "red" to the entity with id 1. So when I want to load an entity for a group I want to load the entity object plus all the custom attributes assigned to the entity but the group. Also I need some mechanism mark an entity as loadable for the group, for this I am using a record like GroupEntityAttributes(group_id=1, entity_id=1, key="_has_connection", value=Null), so *key* *"_*has_connection*"* has a special meaning. If it presents, then the entity should be loaded for the group with all other custom attributes, otherwise no. For this purpose I created a model GroupEntity.
So this way GroupEntity.query.filter_by(group_id=1).all() will give a list with all entities loadable fir the Group 1. Every item of the list will have an association to Group object and Entity object, I want *others* property to represent a list of customer attributes, that I would like to implement as described here (https://bitbucket.org/zzzeek/sqlalchemy/src/374173e89d4e21a75bfabd8a655d17c247b6f1fc/examples/vertical/dictlike.py?at=master). For this I need to have a self referencing relationship to GroupEntityAttributes, which appears to be self-referencing because two models use the same table. SimpleModel is just a mixin that I use in every model I create to make sure that they all have a unique id. Thanks, Anton. On Wednesday, September 10, 2014 3:00:39 PM UTC-7, Michael Bayer wrote: > > > On Sep 10, 2014, at 5:11 PM, Anton <ansch...@gmail.com <javascript:>> > wrote: > > So I am trying to do the following: > class SimpleModel(object): > __table_args__ = {'extend_existing': True} > > id = db.Column(db.Integer, primary_key=True) > > class GroupEntityAttributes(SimpleModel, db.Model): > __table_args__ = {'extend_existing': True} > __tablename__ = 'group_entity' > > #Table Columns > id = db.Column(db.Integer, primary_key=True) > group_id = db.Column(db.Integer, db.ForeignKey('groups.id'), nullable= > False) > entity_id = db.Column(db.Integer, db.ForeignKey('entities.id'), > nullable=False) > key = db.Column(db.String) > value = db.Column(db.Text) > > #Relationships > entity = db.relationship('Entity', lazy='joined') > group = db.relationship('Group') > > class GroupEntityAttributesPoly(GroupEntityAttributes): > __mapper_args__ = {'polymorphic_on': GroupEntityAttributes.key} > > class GroupEntity(GroupEntityAttributesPoly): > __mapper_args__ = {'polymorphic_identity': '_has_connection'} > > others = db.relationship( > 'GroupEntityAttributes', > primaryjoin=and_( > remote(foreign(GroupEntityAttributes.group_id)) == > GroupEntityAttributes.group_id, > remote(foreign(GroupEntityAttributes.entity_id)) == > GroupEntityAttributes.entity_id, > ) > ) > > > > What’s illustrated here doesn’t really make sense. If > GroupEntityAttributes.group_id referred to “groups.id”, then a > relationship extending from GroupEntity would need to refer to whatever > class is mapped to the “entities” table, which I don’t see here. It looks > like you’re taking foreign key columns on the “group_entity” table and > somehow pointing it to itself in that primaryjoin which doesn’t make sense. > GroupEntity would need to be mapped to either “entities” or “groups” in > order to refer to GroupEntityAttributes using that pattern and this sort of > looks like the association object pattern, see > http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object > > for an example of what that looks like. > > Other things that you probably don’t want: > > 1. polymorphic_on on a subclass, that should always go on the mapped class > at the bottom of the hierarchy, see > http://docs.sqlalchemy.org/en/rel_0_9/orm/inheritance.html#joined-table-inheritance > > for basic examples > > 2. extend_existing, this is usually only relevant when you’re using > reflection, otherwise it probably means you’re setting things up > incorrectly > > > > > > > > > > > When I try to load the model GroupEntity I get an error: > ArgumentError: Can't determine relationship direction for relationship ' > GroupEntity.others' - foreign key columns within the join condition are > present in both the parent and the child's mapped tables. Ensure that only > those columns referring to a parent column are marked as foreign, either > via the foreign() annotation or via the foreign_keys argument. > > I am not sure how to overcome this error. Would appreciate any help. > > Best, > Anton. > > On Tuesday, September 9, 2014 1:10:40 PM UTC-7, Anton wrote: >> >> Trying to use with multiple table mapping. >> >> On Tuesday, September 9, 2014 1:03:25 PM UTC-7, Anton wrote: >>> >>> Hi, >>> >>> I need some help choosing the right pattern for the models I have. I >>> have the following tables: >>> Entity (INT id, VARCHAR name) >>> Group (INT id, VARCHAT name) >>> GroupEntityAttributes(INT entity_id, INT group_id, VARCHAR key, VARCHAR >>> name) >>> >>> Entity and Group models are pretty straight forward. But >>> GroupEntityAttributes represents Entity within some group with custom >>> attributes added, so it resembles this pattern ( >>> https://bitbucket.org/zzzeek/sqlalchemy/src/374173e89d4e21a75bfabd8a655d17c247b6f1fc/examples/vertical/dictlike.py?at=master >>> >>> ), the only difference is that there is an additional foreign key. >>> I wonder if there is any approach I can use to adapt vertical pattern >>> for my needs without modifying tables. >>> >>> Best, >>> Anton. >>> >>> >>> > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
