hi Anton - if you can show me correct table designs, e.g. as simple CREATE TABLE statements, that definitely do the thing you need, I can show you how to map to it. So far it's not clear if you're looking for schema design via SQLAlchemy's API.
- mike On Sep 10, 2014, at 6:36 PM, Anton <anschat...@gmail.com> wrote: > 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> 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. >> To post to this group, send email to sqlal...@googlegroups.com. >> 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. -- 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.
