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 <javascript:>>
> 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 <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.
