Self-Referential Association Relationship SQLalchemy

[Mohammad Reza Kamalifard]

In my application with SQLAlchemy i need to create many to many relationship between two contact object also sotre data for each of relatioship here is my Contact model

class Contact(db.Model):
    __tablename__ = 'contact'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Unicode(120), nullable=False, unique=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    to_contacts = db.relationship('Contact',
                                  secondary='ContactRelation',
                                  primaryjoin='id== ContactRelation.from_contact_id',
                                  secondaryjoin='id== ContactRelation.to_contact_id',

                                  backref='from_contacts')




and my association class ContactRelation:

class ContactRelation(db.Model):
    __tablename__ = 'contactrelation'
    id = db.Column(db.Integer, primary_key=True)
    from_contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))
    to_contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))
    relation_type = db.Column(db.String(100), nullable=True)

every thing seems good but i have error :

AttributeError: type object 'ContactRelation' has no attribute 'c'







[Michael Bayer]

table name, not class name, please see http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative.html#declarative-many-to-many

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

[Mohammad Reza Kamalifard]

Using contactrelation.from_contact_id ?

I have AttributeError: 'Table' object has no attribute 'from_contact_id' Error.

> You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/OeLrx74p5vY/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Michael Bayer]

to_contacts = db.relationship('Contact',

           secondary='contactrelation',

           primaryjoin='id== ContactRelation.from_contact_id',

           secondaryjoin='id== ContactRelation.to_contact_id',

or 

to_contacts = db.relationship('Contact',

           secondary=contactrelation,

           primaryjoin=id== contactrelation.c.from_contact_id,

           secondaryjoin=id== contactrelation.c.to_contact_id,           

see examples:

http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#self-referential-many-to-many-relationship

[Mohammad Reza Kamalifard]

thanks Mike 

with new to_contacts relationship i have new error 

ArgumentError: Could not locate any relevant foreign key columns for primary join condition 'contact.id = :param_1' on relationship Contact.to_contacts.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation.

[Simon King]

Could you show the whole model and table definition? I've lost track
of exactly what you've written.

Thanks,

Simon

On Wed, Sep 24, 2014 at 11:15 AM, Mohammad Reza Kamalifard

[Mohammad Reza Kamalifard]

Here is the whole Contact and ContactRelation model

class Contact(db.Model):

    __tablename__ = 'contact'

    id = db.Column(db.Integer, primary_key=True)

    name = db.Column(db.Unicode(120), nullable=False, unique=False)

    created_on = db.Column(db.DateTime, default=datetime.utcnow)

    birthday = db.Column(db.DateTime)

    background = db.Column(db.Text)

    photo = db.Column(db.Unicode(120))

    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    to_contacts = db.relationship('Contact',

           secondary='contactrelation',

           primaryjoin=id== 'contactrelation.c.from_contact_id',

           secondaryjoin=id== 'contactrelation.c.to_contact_id',

           backref = 'from_contacts')

class ContactRelation(db.Model):

    __tablename__ = 'contactrelation'

    id = db.Column(db.Integer, primary_key=True)

    from_contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))

    to_contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))

    relation_type = db.Column(db.String(100), nullable=True)

Thanks,

Mohammad Reza

[Simon King]

OK, firstly I'm not sure you want to be using your contactrelation
table both as a "secondary" as well as a mapped class. You generally
want one or the other. See the note at the bottom of the "Association
Object" section of
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object

For your specific error, you could try defining the primaryjoin and
secondaryjoin conditions like this, but I've honestly no idea if it is
the right approach:

primaryjoin=id=='foreign(contactrelation.c.from_contact_id)',
secondaryjoin=id=='foreign(contactrelation.c.to_contact_id)',

This definitely seems fishy to me though - I'm not sure if SA will
understand that the 2 "id" columns there correspond to 2 different
instances of the Contact class.

Unless you have a particular need for the "secondary" mechanism, I
would rewrite it so that ContactRelation has "from_contact" and
"to_contact" relationships, then use an association proxy to hide the
ContactRelation when you don't need it explicitly:

http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/associationproxy.html


Hope that helps,

Simon


On Wed, Sep 24, 2014 at 2:15 PM, Mohammad Reza Kamalifard

[Mohammad Reza Kamalifard]

Thanks,

I want to create a relationship between two contact object and add more data like relation type to the relation table.

For example Contact with contact id 1 and contact id 2 has friend relationship type and some more data.

Exactly i want a self association relation.

[Simon King]

Right, so it would start something like this:

class Contact(db.Model):
__tablename__ = 'contact'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Unicode(120), nullable=False, unique=False)
created_on = db.Column(db.DateTime, default=datetime.utcnow)
birthday = db.Column(db.DateTime)
background = db.Column(db.Text)
photo = db.Column(db.Unicode(120))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

to_contacts = association_proxy('to_relations', 'to_contact')
from_contacts = association_proxy('from_relations', 'from_contact')

class ContactRelation(db.Model):
__tablename__ = 'contactrelation'
id = db.Column(db.Integer, primary_key=True)
from_contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))
to_contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))
relation_type = db.Column(db.String(100), nullable=True)

from_contact = db.relationship(Contact,
primaryjoin=(from_contact_id == Contact.id),
backref='to_relations')
to_contact = db.relationship(Contact,
primaryjoin=(to_contact_id == Contact.id),
backref='from_relations')


Hope that helps,

Simon

On Wed, Sep 24, 2014 at 8:27 PM, Mohammad Reza Kamalifard