SQLAlchemy, eager load postgresql INET/CIDR relationships

Question

I have two tables in postgresql, one named ip_address, and another named network.

The ip_address table has two columns:

1. id is an INTEGER
2. v4address is an INET

The network table has two columns:

1. id is an INTEGER
2. v4representation is a CIDR

I want to be able to select an ip_address and eagerly load the ip_addresses network(s) without having to define an id based foreign key relationship between the tables. The id columns are used by other, unrelated relationships to other tables.

The SQL to accomplish this is:

select * from ip_address join network on ip_address.v4address << network.v4representation;

In postgresql, the << operator can be used to compare an INET and a CIDR. It will match rows where the INET is contained within the CIDR.

I can define a property on my IPAddress model which will accomplish this:

@property
def networks(self):
    query = session.query(Network)
    query = query.filter("v4representation >> :v4addr").params(v4addr=self.v4address)
    return query.all()

This works, but then when I'm actually attempting to use this property in an application, I experience the typical "N + 1" queries problem. I'd like to define this in such a way as to be able to eager load an ip addresses networks.

I've tried to define it as a relationship using primaryjoin, but can't figure out what's needed. I've tried this:

networks = db.relationship("Network",
                           primaryjoin='IPAddress.v4address << Network.v4representation',
                           viewonly=True)

But sqlalchemy doesn't know what to do with the << operator, so I've switched to this:

networks = db.relationship("Network",
                           primaryjoin='IPAddress.v4address.op("<<")(Network.v4representation)',
                           viewonly=True)

But sqlalchemy throws an ArgumentError:

ArgumentError: Could not locate any relevant foreign key columns for primary join condition 'public.ip_address.v4address << public.network.v4representation' on relationship IPAddress.networks.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation.

I've tried several combinations of defining a foreign_key for the relationship:

networks = db.relationship("Network",
                           primaryjoin='IPAddress.v4address.op("<<")(Network.v4representation)',
                           foreign_keys='[Network.v4representation]',
                           viewonly=True)

But sqlalchemy throws ArgumentErrors: ArgumentError: Relationship IPAddress.networks 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.

Neither specifying IPAddress.v4address or Network.v4representation as a remote_side changes the exception.

No attempts at annotating the primaryjoin condition with foreign/remote has helped either.

Coming back to my original intent, I want to be able to perform a query which will return ip addresses and eager load their networks (and possibly data from the networks other relations, as this is a simplification of my full schema).

Does anyone have any suggestions?

Thanks in advance.

Answer 1

the missing piece here is that the custom operator isn't working within the relationship framework. To assist in this case I've added a new feature for SQLAlchemy 0.9.2 which is the "is_comparison" flag, and an example is here, at Using custom operators in join conditions.

Here is a version that accomplishes this same result using less public APIs, which will work in 0.8 also:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.dialects.postgresql import INET, CIDR

Base = declarative_base()

# workaround before 0.9.2
from sqlalchemy.sql import operators
is_contained_by = operators.custom_op("<<")
operators._comparison.add(is_contained_by)

class IPA(Base):
    __tablename__ = 'ip_address'

    id = Column(Integer, primary_key=True)
    v4address = Column(INET)

    network = relationship("Network",
                        primaryjoin=lambda: is_contained_by(
                                     IPA.v4address, 
                                     (foreign(Network.v4representation))
                                    ),
                        viewonly=True
                    )
class Network(Base):
    __tablename__ = 'network'

    id = Column(Integer, primary_key=True)
    v4representation = Column(CIDR)

print Session().query(IPA).join(IPA.network)

In 0.9.2 and greater, it can be done as:

class IPA(Base):
    __tablename__ = 'ip_address'

    id = Column(Integer, primary_key=True)
    v4address = Column(INET)

    network = relationship("Network",
                        primaryjoin="IPA.v4address.op('<<', is_comparison=True)"
                            "(foreign(Network.v4representation))",
                        viewonly=True
                    )