1

I have a mixin class that I define near the beginning of the SQLAlchemy app and then inherit on pretty much every declarative model I use.

class Owned(object):

    @declared_attr
    def created_by_id(cls):
        return Column(Integer, ForeignKey("accounts.id"),
            nullable = True)

    @declared_attr
    def created_by(cls):
        return relationship("Account", foreign_keys = cls.created_by_id)

    @declared_attr
    def updated_by_id(cls):
        return Column(Integer, ForeignKey("accounts.id"),
            nullable = True)

    @declared_attr
    def updated_by(cls):
        return relationship("Account", foreign_keys = cls.updated_by_id)

This works well for most intended use cases.

class Thing(Owned, Base): # Base is from SQLAlchemy's declarative_base()
    pass

account = session.query(Account).first()

thing = Thing(created_by = account, updated_by = account)

session.add(thing)
session.commit()
session.refresh(thing)

assert thing.created_by == account # pass
assert thing.updated_by == account # pass

However, I get unexpected behaviour when I define Account itself as inheriting from Owned.

class Account(Owned, Base):
    pass

account_old = session.query(Account).first()

account_new = Account(created_by = account_old, updated_by = account_old)

session.add(account_new)
session.commit()
session.refresh(account_new)

assert account_new.created_by_id == account_old.id # pass
assert account_new.updated_by_id == account_old.id # pass

# BUT!

assert account_new.created_by == account_old # fail
assert account_new.updated_by == account_old # fail

account_new.created_by # []
account_new.updated_by # []

I see that, in this case, I've turned created_by_id and updated_by_id into self-referential foreign keys. What I don't understand, however, is why SQLAlchemy isn't populating their associated relationship columns with the expected Account instances.

What am I doing wrong?

snoopy91
  • 337
  • 3
  • 12

1 Answers1

0

In an adjacency list relationship the "direction" is assumed by default to be one-to-many. Using the remote_side directive establishes that the relationship is many-to-one, which is what you're after:

def _create_relationship(cls, foreign_keys):
    kwgs = {}
    # Bit of a chicken or egg situation:
    if cls.__name__ == "Account":
        kwgs["remote_side"] = [cls.id]

    return relationship("Account", foreign_keys=foreign_keys, **kwgs)


class Owned:

    @declared_attr
    def created_by_id(cls):
        return Column(Integer, ForeignKey("accounts.id"),
            nullable = True)

    @declared_attr
    def created_by(cls):
        return _create_relationship(cls, cls.created_by_id)

    @declared_attr
    def updated_by_id(cls):
        return Column(Integer, ForeignKey("accounts.id"),
            nullable = True)

    @declared_attr
    def updated_by(cls):
        return _create_relationship(cls, cls.updated_by_id)
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
  • Thanks so much. I followed your approach and am getting this error: `sqlalchemy.exc.ArgumentError: Relationship Account.created_by could not determine any unambiguous local/remote column pairs based on join condition and remote_side arguments. Consider using the remote() annotation to accurately mark those elements of the join condition that are on the remote side of the relationship.` Do I have to specify the join manually? Or is it that `foreign_keys` is only being recognised in one direction? – snoopy91 Jun 05 '18 at 13:23
  • Hmm, I think a bit more context would be in order. Given the 2 relationship attributes the mixin worked in my local tests and should not require any manual joins. – Ilja Everilä Jun 05 '18 at 13:25