postgresql 非相等联接的Rails ActiveRecord关联

我们有一个Rails应用程序，可以跟踪部门资产注册，包括子网和IP地址。部门与子网和IP都有一对多的关联。除了显示部门的IP（在其他部门的子网上，如下图@dept_ips所示），我们还需要显示该部门子网上其他部门的IP（如下图@others_ips所示）。
部门型号：

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

子网模型使用以下方法获取IP地址：

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

在IP模型中引用此方法：

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

对于子网和IP地址，也有相关信息：

注册的子网链接到其他表，例如描述性信息、防火墙信息等。
注册的IP链接到其他表：主机名、敏感数据级别等

加载其他人的IP地址和相关信息的查询非常慢。使用快速加载有所帮助，但索引页的加载速度仍然很慢。@dept_ips加载正常。
第一个
由于SQL可以生成所需的信息，我尝试使用带有服务和实体的原始SQL。这很有效，但我无法让系统测试工作，因为实体没有'dom_id'。或者至少我不知道如何为实体创建'dom_id'。

我真正想要的是一个使用非相等连接的Rails关联。

虽然可以在Rails中编写自定义连接，

@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")

...协会总是基于平等：https://guides.rubyonrails.org/association_basics.html
(FYI，非对等关系实际上是相当有用的：（https://learnsql.com/blog/sql-non-equi-joins-examples/）
具体来说，我需要PostgreSQL inet和cidr数据类型之间的非相等连接，特别是'contained by'和'contains'操作符：

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

有关inet和cidr数据类型以及运算符'<<'（包含于）和'>>'（包含）的详细信息，请参阅PostgreSQL文档https://www.postgresql.org/docs/14/functions-net.html。

使用活动记录，当关系不是基于相等时，无法在两个模型之间创建“具有许多/属于”关联。

当我们需要将IP地址或子网（或两者）与其他表关联时，自定义连接的性能不佳。
解决方案是在IP地址和子网之间建立一个交集表。但是，由于IP地址来来去去，而且每当子网大小改变（即掩码长度改变）时，它们所包含的子网也会改变，因此维护一个实际的交集表是不切实际的。答案是什么？一个数据库视图、一个只读模型和has_one_through、has_many_through关联。
1.使用非相等联接定义数据库视图：

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;

1.并创建表示该视图的只读模型：

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

1.最后，在子网和IP模型中，使用has_one_through,has_many_through关系将IP地址连接到子网：
第一个
瞧！

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

该解决方案与原来的解决方案一样简单易懂，但性能更好。

postgresql 非相等联接的Rails ActiveRecord关联

1条答案

相关问题

热门标签

最新问答