Ruby ActiveRecord 数据分组和连表查询

ciscolive · 2021年07月26日 · 332 次阅读

需求场景:查询网络交换机端口(关联物理线路)的带宽使用情况,体现带宽利用率和峰值。 数据逻辑:多台设备接入不同运营商的专线,具体到每条线路存在进出方向的带宽利用率。需要基于交换机和物理接口分组输出相关的数据,数据要绑定到具体的设备接口,基于物理设备聚合数据。 联结关系:host_item ->(has_many) host_histories, host_histories ->(belongs_to) host_item;

HostItem.group(:hostname).group(:iface, :id).having("descr like ?", "%peng%").order(:iface).as_json
  HostItem Load (1.7ms)  SELECT "host_items".* FROM "host_items" GROUP BY "host_items"."hostname", "host_items"."iface", "host_items"."id" HAVING (descr like '%peng%') ORDER BY "host_items"."iface" ASC
=> [{"id"=>39,
  "hostname"=>"SZX1-SW7",
  "ip"=>"10.250.8.251",
  "host_id"=>15855,
  "iface_id"=>9,
  "iface"=>"GigabitEthernet1/0/9",
  "descr"=>"UPSTREAM:100M_Dr.peng",
  "snmp_oid"=>"1.3.6.1.2.1.31.1.1.1.6.9",
  "ref_bandwidth"=>100,
  "created_at"=>"2021-07-26T11:37:48.826+08:00",
  "updated_at"=>"2021-07-26T11:37:48.826+08:00"},
 {"id"=>40,
  "hostname"=>"SZX1-SW7",
  "ip"=>"10.250.8.251",
  "host_id"=>15855,
  "iface_id"=>9,
  "iface"=>"GigabitEthernet1/0/9",
  "descr"=>"UPSTREAM:100M_Dr.peng",
  "snmp_oid"=>"1.3.6.1.2.1.31.1.1.1.10.9",
  "ref_bandwidth"=>100,
  "created_at"=>"2021-07-26T11:37:48.829+08:00",
  "updated_at"=>"2021-07-26T11:37:48.829+08:00"}]

# 查询出某接口特定方向的数据
 HostItem.first.host_histories
  HostItem Load (1.3ms)  SELECT "host_items".* FROM "host_items" ORDER BY "host_items"."id" ASC LIMIT $1  [["LIMIT", 1]]
  HostHistory Load (0.8ms)  SELECT "host_histories".* FROM "host_histories" WHERE "host_histories"."host_item_id" = $1  [["host_item_id", 35]]
=> [#<HostHistory:0x0000559decaeeba8
  id: 33,
  min_value: 63730696,
  max_value: 349566248,
  avg_value: 197222665,
  avg_utilization: 0.3944453304,
  max_utilization: 0.699132496,
  created_at: Mon, 26 Jul 2021 11:37:48.799527000 CST +08:00,
  updated_at: Mon, 26 Jul 2021 11:37:48.799527000 CST +08:00,
  host_item_id: 35>]

折腾半天,还是没想明白怎么通过 active_model 查询出上面的数据结构,麻烦大佬们帮忙看下,谢谢。

期望输出的数据结构:

{
  hostname: "HOST_A",
  interface eth1 : {
    in: {avg:100}
    out: {avg:200}
 }
}
暂无回复。
需要 登录 后方可回复, 如果你还没有账号请 注册新账号