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