I already have models for group, entity and group_entity. Now I am trying to build a model that would represent an entity for a group with all the custom attributes.
On Wednesday, September 10, 2014 5:06:36 PM UTC-7, Anton wrote: > > Mike, the tables are already there, I am trying to create models mapped on > the tables so it is easier to work with. > Tables design looks like: > > CREATE TABLE group ( > id integer NOT NULL, > name character varying > ); > > > CREATE TABLE entity ( > id integer NOT NULL, > name character varying > ); > > CREATE TABLE group_entity ( > id integer NOT NULL, > entity_id integer, > key character varying, > value text, > group_id integer NOT NULL > ); > > Best, > Anton. > > > > > > On Wednesday, September 10, 2014 4:51:54 PM UTC-7, Michael Bayer wrote: >> >> 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 <ansch...@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+...@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.
