Relationship to child with 2 foreginKey from same Parent column

[Alireza Ayin Mehr]

Hello, everyone

I have a User model and a Conversation model

class Conversation(Base):
 __tablename__ = 'conversations'
 id = Column(Integer, primary_key=True)
 user1 = Column(Integer, Foreignusers.id'), unique=False, nullable=False)
 user2 = Column(Integer, ForeignKey('users.id'), unique=False, nullable=False)
 date = Column(DateTime, unique=False, nullable=False, default=dt.utcnow)

class User(Base):
 __tablename__ = 'users'
 id = Column(Integer, primary_key=True)
 userName = Column(String, unique=False, nullable=True)

Im trying to add a relationship() to User model which should return all conversations where Conversation.user1 or Conversation.user2 is the selected User




I tried:

conversations = relationship('Conversation', backref="Users")

Which results to this error when I was trying to get the User:

AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship User.conversations - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

I realized I have to specify columns..

So I tried:

conversations = relationship(Conversation, foreign_keys=[Conversation.user1, Conversation.user2], backref="Users")

and

conversations = relationship(Conversation, foreign_keys='[Conversation.user1, Conversation.user2]', backref="Users")

and again same error!

After that I tried:

conversations = relationship(Conversation, foreign_keys=Conversation.user1 or Conversation.user2, backref="Users")

Which returned this error on commiting my Conversation object:

RecursionError: maximum recursion depth exceeded

Again I tried to use this:

conversations = relationship(Conversation, foreign_keys=['Conversation.user1', 'Conversation.user2'], backref="Users")

And it gave me this error on getting the User object:

ArgumentError: Column-based expression object expected for argument 'foreign_keys'; got: 'Conversation.user1', type class 'str'

Also I tried to get only one specific column by defining it using foreign_keys but it gave me the same RecursionError

I know I can do it by adding a method to User class like:

def conversations(self): return session.query(Conversation).filter(Conversation.user1 == self.id or Conversation.user2 == self.id).all()

But it's important for me to do it using relationship()..

[Jonathan Vanasco]

I believe something like this should work. 

    conversations = sqlalchemy.orm.relationship("Conversation",
                                                primaryjoin="""or_(User.id==Conversation.user_id_1,
                                                                   User.id==Conversation.user_id_2,
                                                                   )""",
                                                order_by="Conversation.id.desc()",
                                                )

i also think you'll need to make an explicit relationship for a user1 and user2 relationship instead of using back_populates.  i could be wrong. personally i would make separate relationships though, because having an undordered list for them makes little sense. 

[Alireza Ayin Mehr]

Well, I tried "or_()" before

primaryjoin="or_(User.id == Conversation.user1, User.id == Conversation.user2)"

It returns RecursionError :(

[Mike Bayer]

is there a stack trace (just the beginning and then a bit of the
repeating part, not the whole thing), there's no recursion inherent in
the SQLAlchemy part of this so something must be up with your model.
an MCVE (see the link below) is always the best way to show what's
happening.

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Alireza Ayin Mehr]

It's riding me crazy

here is a MVCE

https://paste.ofcode.org/7evqFGabM3Ls8qXiNnTGSy

[Alireza Ayin Mehr]

Using or_ function

https://paste.ofcode.org/zd7aqqwR4V4rpYjfpmCHQg

On Friday, August 24, 2018 at 2:26:21 AM UTC+4:30, Mike Bayer wrote:

[Seth P]

In relationship(), foreign_keys refers to the field in the source table, not the destination.

[Alireza Ayin Mehr]

Well, it seems weird this way, I only have one userId in my parent class which is the source class

[Simon King]

Here's a version which I *think* does what you want. There are a
couple of things that you might want to note.

First, in your paste, Conversation.user1 and Conversation.user2 are
integer columns, but you are assigning User objects to those
properties. That's not the way SQLAlchemy works - you can't assign a
related object directly to the column. In my version below I've
renamed the columns to "userid1" and "userid2" and then created
relationships for "user1" and "user2".

Second, the User.conversations property needs to have "viewonly=True".
It wouldn't make sense for it to be a writable property, because if
you appended a new Conversation to it, SQLAlchemy wouldn't know which
of the userid1 or userid2 columns to update.


####################

import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Conversation(Base):
__tablename__ = 'conversations'

id = sa.Column(sa.Integer(), primary_key=True)
userid1 = sa.Column(
sa.Integer(),
sa.ForeignKey('users.id'),
nullable=False,
)
userid2 = sa.Column(
sa.Integer(),
sa.ForeignKey('users.id'),
nullable=False,
)

user1 = saorm.relationship(
"User",
primaryjoin="Conversation.userid1 == User.id",
)
user2 = saorm.relationship(
"User",
primaryjoin="Conversation.userid2 == User.id",
)

class User(Base):
__tablename__ = 'users'

id = sa.Column(sa.Integer(), primary_key=True)
userName = sa.Column(sa.String(32), unique=True, nullable=False)

conversations = saorm.relationship(
Conversation,
primaryjoin=sa.or_(
id == Conversation.userid1,
id == Conversation.userid2,
),
viewonly=True,
)


if __name__ == '__main__':
engine = sa.create_engine("sqlite:///:memory:", echo="debug")
Session = saorm.sessionmaker(bind=engine)
Base.metadata.create_all(engine)

session = Session()
u1 = User(userName="Alireza")
u2 = User(userName="Amir")
session.add_all([u1, u2])
session.commit()

con = Conversation(user1=u1, user2=u2)
session.add(con)
session.commit()

print u1.conversations


####################

[Alireza Ayin Mehr]

Thanks a lot

This works, I cant remember why I was passing the User object as the argument

How can I mark a post as the answer here?