Problem with between join for sqlalchemy orm relation.

Question

I'm trying to create a relation that has a between join. Here is a shortish example of what I'm trying to do:

#!/usr/bin/env python
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])    

metadata.create_all(engine) 

Session = orm.sessionmaker(bind=engine)

Network()

If you run this, you will get this error:

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 ?

The answer to that question is Yes, but I cant figure out how to tell it that

Answer 1

SQLAlchemy traverses condition to find local-remote pairs in it to determine columns and cardinality of the relation. This algorithm is designed to work for binary operators only. The simple way to solve your case is to rewrite BETWEEN with two operators. And not, that they are not "equal" operators, so you can't use this relation to append new addresses, that's why viewonly=True is used:

Network.addresses = orm.relation(Address,
    viewonly=True,
    primaryjoin=(
        (Address.ip_addr_db>=Network.ip_net_addr_db) &
        (Address.ip_addr_db<=Network.ip_broadcast_addr_db)
    ),
    foreign_keys=[Address.ip_addr_db]
)