Can I join the column names with search_related in DBIx?

Question

I have a DBIx Class schema where I have;

• A Device that has many Interfaces

• An Interface has many Rules Applied

• Each Rule has many Rule Entries.

I want to search for all of the Rule Entries for a Particular device name and Rule Name.

I am still learning DBIx so I don’t know if this is even the most efficient way.

I am doing this like so;

my $rs = $self->search( { devicename => ‘DeviceA’ } )->search_related('interfaces')->search_related(’Rules’, { rulename => ‘RuleA’ } )->search_related(‘RuleEntries’, {},  
                        { columns => [qw/source destination port/], result_class => 'DBIx::Class::ResultClass::HashRefInflator'} );

What I am trying to do is get the ‘RuleName’ as a column of my result set.

at the moment I’m getting all of the Rule Entries for DeviceA with a RuleName on an interface called RuleA, The columns returned are

  ‘source destination port’. 

I want this to look like

 ‘rulename source destination port’

Answer 1

As you are already restricting the rule name it doesn't make sense to query it from the database.

Besides that you should always search for objects of the type you want to get back, in your case that's rule entries:

my $rs = $schema->resultset('Rule_Entries')->search({
    'rel_device.name' => 'DeviceA',
    'rel_rule.name' => 'Rule',
},{
    columns => [ 'rel_rule.name', 'me.source', 'me.destination', 'me.port' ],
    join => { rel_rule => { rel_interface => 'rel_device' }},
});

It seems your doing something very similar what I do: storing firewall rules. You might want to have the rule directly related to the device and the interface being an optional attribute of the rule because some vendors don't have interface specific rules (Checkpoint).