Rails ActiveRecord Associations for Non-Equi Joins

Question

We have a Rails app that tracks departments asset registrations including subnets and IP addresses. Departments have one to many associations to both Subnets and IPs. In addition to displaying a department's IPs (on other's subnets, @dept_ips below) we need to display other departments' IPs on this department's subnets (@others_ips below.)

Department model:

class Department < ApplicationRecord
  
  has_many(:subnets, class_name: "Subnet", foreign_key: :department_id)
  has_many(:ips, class_name: "Ip", foreign_key: :department_id)
    ...
end

The Subnets model had this method to get IP addresses:

class Subnet < ApplicationRecord
  def ips
    Ip.for_subnet(subnet)
  end
  ...
end

Referencing this method in the IP model:

class Ip < ApplicationRecord
  def self.for_subnet(subnet)
    where("ip << '#{subnet.to_cidr}'")
  end
  ...
end

For both subnets and IP addresses there is also related information:

• Registered Subnets link to other tables, e.g., descriptive info, firewall info, etc.
• Registered IPs link to other tables: hostnames, level of sensitive data, etc.

The query to load Other's IP addresses and related info is very slow. Using eager load helped, but the index pages are still slow to load. @dept_ips loads just fine.

 @others_ips = @department.subnets
                          .eager_load(ips:[:calc_ip, 
                                           :host, 
                                           {subnet: :fw_subnet}] )
                          .order('ip.ip')
                          .map(&:ips)
                          .flatten

 @dept_ips = @department.ips
                        .eager_load(:calc_ip,
                                    :host, 
                                    {subnet: :fw_subnet})
                        .order('ip.ip')

Since SQL can produce the required information I tried using raw sql with a service and entity. This worked but I couldn't get a system test to work because the entity didn't have a 'dom_id'. Or at least I couldn't figure out how to create a 'dom_id'for the entity.

Answer 1

What I really want is a Rails association that uses a non-equi join.

While one can write a custom join in Rails. e.g.,

 @subnet = Subnet.find_by(subnet: 'cidr')
 @subnet_ips = @subnet.joins("inner join ips on ip <<= subnet")
 @ip = @subnet_ips.first 
 
 @containing_subnet = @ip.joins("inner join subnets on subnet >>= ip")

... associations are always based on equality: https://guides.rubyonrails.org/association_basics.html

(FYI, non-equi relationships are actually quite useful: https://learnsql.com/blog/sql-non-equi-joins-examples/)

Specifically I needed an non-equi join between PostgreSQL inet and cidr data types, specifically the ‘contained by’ and ‘contains’ operators:

  SELECT * FROM IPs 
  INNER JOIN Subnets
  ON IPs.ip << Subnets.subnet;

See PostgreSQL documentation https://www.postgresql.org/docs/14/functions-net.html for more information about inet and cidr data types, and the operators '<<'(contained by), and '>>'(contains).

With Active Record, it is not possible to create ‘has many / belongs to’ associations between two models when the relationship is not based on equality.

Custom joins were did not perform well when we needed to associate an IP address or a Subnet, or both, to additional tables.

The solution was to build an intersection table between IP addresses and subnets. But since IP addresses came and went, and their containing subnet changed whenever a subnet changed size (i.e., its mask length changed) maintaining an actual intersection table was impractical. The answer? A database view, a read-only model and has_one_through, has_many_through associations.

1. Define database view using the non-equi join:

  CREATE OR REPLACE VIEW ip_subnet_link AS
  SELECT i.id as ip_id, s.id AS subnet_id
  FROM ip_addresses i
  INNER JOIN subnets s ON i.ip << s.subnet;

2. And create a read-only model representing that view:

  class IPSubnetLink < ApplicationRecord
    self.table_name = "ip_subnet_link"
    self.primary_key = "ip_id"
    belongs_to(:subnet, class_name: "Subnet", foreign_key: :subnet_id)
    belongs_to(:ip, class_name: "Ip", foreign_key: :ip_id)

    attribute(:ip_id, :uuid)
    attribute(:subnet_id, :uuid)

    def readonly?
      true
    end
  end

3. Finally in the Subnet and IP models use has_one_through, has_many_through relationships to connect IP addresses to Subnets:

 class Ip < ApplicationRecord

   has_one(:subnet_ip, class_name: "IPSubnetLink", foreign_key: :ip_id)
   has_one(:subnet, through: :subnet_ip, source: :subnet)

   ...
 end

 class Subnet < ApplicationRecord

   has_many(:subnet_ips, class_name: "IPSubnetLink", foreign_key: :subnet_id)
   has_many(:ips, through: :subnet_ips, class_name: "IP")
   
   ...
 end

Et Voila!

  @others_ips = @department.subnets
                           .ips
                           .eager_load(:calc_ip, 
                                       :host, 
                                       {subnet: :fw_subnet})
                           .order('ip.ip')

The solution is simple and as easy to understand as the original, but with better performance.