get local_remote_pairs to work as other "declarable" arguments

[Former user]

Here is an example from StackOverflow showing the problem:

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


metadata = sa.MetaData()
Base = declarative_base(metadata=metadata)

engine = sa.create_engine('sqlite:///:memory:')

class Network(Base):
    __tablename__ =  "network"
    id = sa.Column(sa.Integer, primary_key=True)
    ip_net_addr_db = sa.Column('ip_net_addr', sa.Integer, index=True)
    ip_broadcast_addr_db = sa.Column('ip_broadcast_addr', sa.Integer, index=True)
    # This can be determined from the net address and the net mask, but we store
    # it in the db so that we can join with the address table.
    ip_net_mask_len = sa.Column(sa.SmallInteger)

class Address(Base):
    __tablename__ =  "address"
    ip_addr_db = sa.Column('ip_addr', sa.Integer, primary_key=True,
              index=True, unique=True)

Network.addresses = orm.relation(Address, 
    primaryjoin=Address.ip_addr_db.between(
                Network.ip_net_addr_db,
                Network.ip_broadcast_addr_db),
    foreign_keys=[Address.ip_addr_db](Address.ip_addr_db))

metadata.create_all(engine)

Session = orm.sessionmaker(bind=engine)

Network()

The code above is expected to work when viewonly=True option is used, but it doesn't:

sqlalchemy.exc.ArgumentError: Could not determine relation direction
for primaryjoin condition 'address.ip_addr BETWEEN network.ip_net_addr 
AND network.ip_broadcast_addr', on relation Network.addresses. 
Do the columns in 'foreign_keys' represent only the 'foreign' 
columns in this join condition ?

Even if it's too difficult to provide correct behavior for all possible conditions, there should be at least a way to help SQLAlchemy determining pairs of keys when automatic discovery fails.

[Mike Bayer]

the automatic discovery can be circumvented using the _local_remote_pairs argument to relationship(), in conjunction with foreign_keys. The only reason this option is not publicized is to avoid the "too many ways to do the same thing" criticism.

[Former user]

Setting _local_remote_pairs doesn't seem to work (or I use it wrong way):

Network.addresses = orm.relation(Address,
    viewonly=True,
    primaryjoin=Address.ip_addr_db.between(
        Network.ip_net_addr_db,
        Network.ip_broadcast_addr_db),
    _local_remote_pairs=[Network.ip_net_addr_db),
                         (Address.ip_addr_db, Network.ip_broadcast_addr_db)]((Address.ip_addr_db,),
    foreign_keys=[Address.ip_addr_db](Address.ip_addr_db)
)

raises

sqlalchemy.exc.ArgumentError: Can't determine relation direction for relationship 
'Network.addresses' - foreign key columns are present in neither the parent nor the 
child's mapped tables

[Mike Bayer]

try setting the columns in _local_remote_pairs from table.c. just a guess.

[Former user]

Ok, this works:

    _local_remote_pairs=[                         Network.__table__.c.ip_net_addr),
                         (Address.__table__.c.ip_addr,
                          Network.__table__.c.ip_broadcast_addr)]((Address.__table__.c.ip_addr,
),

But it raises other problems. Shouldn't it accept ModelClass.column too? Shouldn't it become public option?

[Mike Bayer]

Replying to guest:

Shouldn't it accept ModelClass.column too?

yes that is a simple adjustment to the argument reception.

Shouldn't it become public option?

shrugs. It's virtually never needed. It becomes another way of doing what is already possible. Then we have two ways to do the same thing. How do we justify that ? How do we document it ? Which method becomes the "standard" ?

But it raises other problems.

anything else ?

[Mike Bayer]

#1401 addresses this use case. However there appear to be some glitches, one is #2660 being fixed, and another is that the betweeen() operator didn't make it into operators._comparison. should have this case working soon.

[Mike Bayer]

works as of f4f3c56cd31a30e87f17148f3d4d17832c12b110, using this form:

class Network(Base):
    __tablename__ = "network"

    id = sa.Column(sa.Integer, primary_key=True)
    ip_net_addr = sa.Column(sa.Integer)
    ip_broadcast_addr = sa.Column(sa.Integer)

    addresses = relationship("Address",
            primaryjoin="remote(foreign(Address.ip_addr)).between("
                        "Network.ip_net_addr,"
                        "Network.ip_broadcast_addr)",
            viewonly=True
)

class Address(Base):
    __tablename__ = "address"

    ip_addr = sa.Column(sa.Integer, primary_key=True)

[Mike Bayer]

Removing milestone: 0.8.0final (automated comment)