1package App::Netdisco::DB::Result::Virtual::UnDirEdgesAgg;
2
3use strict;
4use warnings;
5
6use base 'DBIx::Class::Core';
7
8__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
9
10__PACKAGE__->table('undir_edges_agg');
11__PACKAGE__->result_source_instance->is_virtual(1);
12__PACKAGE__->result_source_instance->view_definition(<<'ENDSQL');
13   SELECT left_ip,
14          array_agg(right_ip) AS links
15   FROM
16     ( SELECT dp.ip AS left_ip,
17              di.ip AS right_ip
18      FROM
19        (SELECT device_port.ip,
20                device_port.remote_ip
21         FROM device_port
22         WHERE device_port.remote_port IS NOT NULL
23         GROUP BY device_port.ip,
24                  device_port.remote_ip) dp
25      LEFT JOIN device_ip di ON dp.remote_ip = di.alias
26      WHERE di.ip IS NOT NULL
27      UNION SELECT di.ip AS left_ip,
28                   dp.ip AS right_ip
29      FROM
30        (SELECT device_port.ip,
31                device_port.remote_ip
32         FROM device_port
33         WHERE device_port.remote_port IS NOT NULL
34         GROUP BY device_port.ip,
35                  device_port.remote_ip) dp
36      LEFT JOIN device_ip di ON dp.remote_ip = di.alias
37      WHERE di.ip IS NOT NULL ) AS foo
38   GROUP BY left_ip
39   ORDER BY left_ip
40ENDSQL
41
42__PACKAGE__->add_columns(
43  'left_ip' => {
44    data_type => 'inet',
45  },
46  'links' => {
47    data_type => 'inet[]',
48  }
49);
50
51__PACKAGE__->belongs_to('device', 'App::Netdisco::DB::Result::Device',
52  { 'foreign.ip' => 'self.left_ip' });
53
541;
55