Ambiguous join using SQLAlchemy and Flask

Question

I am having a problem at the moment with joins on a query between two SQLAlchemy tables. I am building an application through Python and Flask that allows a user to create a task and assign it to another user. The problem is that the sender and recipient of these tasks use a foreign key from the same column of the same table. For example, the sender_id uses a user_id from the User table, and the recipient_id uses a different user_id from the same table. I have tried using aliases for my query but it doesn't seem to make a difference. The error I get is:

sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'user' and 'task'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

Which causes the following exception:

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship User.assigned_tasks - 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 am fairly unfamiliar with SQLAlchemy so I may be missing something important, or using something wrong. Here's the code:

Models:

class User(db.Model):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(64))
    password = db.Column(db.String(64))
    forename = db.Column(db.String(64))
    surname = db.Column(db.String(64))
    company = db.Column(db.String(100))
    assigned_tasks = db.relationship("Task", back_populates="user")

    def to_dict(self):
        return {
            'id' : self.id,
            'username' : self.username,
            'password' : self.password,
            'forename' : self.forename,
            'surname' : self.surname,
            'company' : self.company
        }

class Task(db.Model):
    __tablename__ = "task"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    sender_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    recipient_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    subject = db.Column(db.String(64))
    description = db.Column(db.Text())
    urgency = db.Column(db.String(20))
    date_created = db.Column(db.DateTime)
    date_required = db.Column(db.DateTime)
    date_completed = db.Column(db.DateTime)
    users = db.relationship("User", foreign_keys=[sender_id, recipient_id])

    def to_dict(self):
        return {
            'id' : self.id,
            'sender_id' : self.sender_id,
            'recipient_id' : self.recipient_id,
            'subject' : self.subject,
            'description' : self.description,
            'urgency' : self.urgency,
            'date_created' : self.date_created,
            'date_required' : self.date_required,
            'date_completed' : self.date_completed
        }

Query:

sender_user = aliased(User)
recipient_user = aliased(User)
query = Task.query(Task.id, Task.subject, Task.description, Task.urgency,
                   functions.concat(recipient_user.forename, " ", recipient_user.surname),
                   functions.concat(sender_user.forename, " ", sender_user.surname),
                   Task.date_created, Task.date_required, Task.date_completed
                   ).join(User, (Task.recipient_id == recipient_user.id) & (Task.sender_id == sender_user.id))

If anyone could give any pointers on what I might be doing wrong, it would be greatly appreciated. Happy to give any more information if needed.

Thanks in advance!

Answer 1

This is an old post, so I am not at all sure this will be useful to you, but the problem starts before the sqlalchemy error. Your boss-worker pair fashioned out of User records prevents the task from being assigned by the same boss to the same worker more than once, and it relies on a complex foreign key. That, in turn, triggers an error. You may be better off creating either separate Boss and Worker tables or creating a table Team of Boss-Worker pairs with a unique ID, which can be associated with Task through a different association table Assignment. sqlalchemy will be very straightforward in that case.