title: SQLAlchemy, eager load postgresql INET/CIDR relationships
body: <p>I have two tables in postgresql, one named <code>ip_address</code>, and another named <code>network</code>.</p>
  
  <p>The <code>ip_address</code> table has two columns:</p>
  
  <ol>
  <li><code>id</code> is an <code>INTEGER</code></li>
  <li><code>v4address</code> is an <code>INET</code></li>
  </ol>
  
  <p>The <code>network</code> table has two columns:</p>
  
  <ol>
  <li><code>id</code> is an <code>INTEGER</code></li>
  <li><code>v4representation</code> is a <code>CIDR</code></li>
  </ol>
  
  <p>I want to be able to select an <code>ip_address</code> and eagerly load the <code>ip_addresses</code> <code>network</code>(s) without having to define an <code>id</code> based foreign key relationship between the tables. The <code>id</code> columns are used by other, unrelated relationships to other tables.</p>
  
  <p>The SQL to accomplish this is:</p>
  
  <pre><code>select * from ip_address join network on ip_address.v4address &lt;&lt; network.v4representation;
  </code></pre>
  
  <p>In postgresql, the <code>&lt;&lt;</code> operator can be used to compare an <code>INET</code> and a <code>CIDR</code>. It will match rows where the <code>INET</code> is contained within the <code>CIDR</code>.</p>
  
  <p>I can define a <code>property</code> on my <code>IPAddress</code> model which will accomplish this:</p>
  
  <pre><code>@property
  def networks(self):
      query = session.query(Network)
      query = query.filter("v4representation &gt;&gt; :v4addr").params(v4addr=self.v4address)
      return query.all()
  </code></pre>
  
  <p>This works, but then when I'm actually attempting to use this <code>property</code> 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.</p>
  
  <p>I've tried to define it as a <code>relationship</code> using <code>primaryjoin</code>, but can't figure out what's needed. I've tried this:</p>
  
  <pre><code>networks = db.relationship("Network",
                             primaryjoin='IPAddress.v4address &lt;&lt; Network.v4representation',
                             viewonly=True)
  </code></pre>
  
  <p>But sqlalchemy doesn't know what to do with the <code>&lt;&lt;</code> operator, so I've switched to this:</p>
  
  <pre><code>networks = db.relationship("Network",
                             primaryjoin='IPAddress.v4address.op("&lt;&lt;")(Network.v4representation)',
                             viewonly=True)
  </code></pre>
  
  <p>But sqlalchemy throws an <code>ArgumentError</code>:</p>
  
  <pre><code>ArgumentError: Could not locate any relevant foreign key columns for primary join condition 'public.ip_address.v4address &lt;&lt; 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.
  </code></pre>
  
  <p>I've tried several combinations of defining a <code>foreign_key</code> for the <code>relationship</code>:</p>
  
  <pre><code>networks = db.relationship("Network",
                             primaryjoin='IPAddress.v4address.op("&lt;&lt;")(Network.v4representation)',
                             foreign_keys='[Network.v4representation]',
                             viewonly=True)
  </code></pre>
  
  <p>But sqlalchemy throws <code>ArgumentErrors</code>:
      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.</p>
  
  <p>Neither specifying <code>IPAddress.v4address</code> or <code>Network.v4representation</code> as a <code>remote_side</code> changes the exception. </p>
  
  <p>No attempts at annotating the <code>primaryjoin</code> condition with <code>foreign</code>/<code>remote</code> has helped either.</p>
  
  <p>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).</p>
  
  <p>Does anyone have any suggestions?</p>
  
  <p>Thanks in advance.</p>