[Dbix-class] Dealing with relationships of non-order sensitive value pairs

[Guillermo Roditi]

Hey everyone.

I am dealing with a situation where I have a relationship between a
table and itself representing a connection between two items. This is
analogous to a "friend of" relationship, but since relationships must
be two-ways in my application they are not order sensitive. Now, i
understand that to fetch all related objects (B) from an object (A) I
can simply order the PKs and search enforcing that order. It's a
simple solution, it works, but I don't trust future users of my
application to not be morons, so I came up with the Following
solution, I'd like some input from people who have dealt with this in
the past because I know someone out there already hit an issue I
didn't think about or made the the same mistakes I am making or is
just p[lain smarter


I am working on a DBIx::Class-backed DJabberd system with a Catalyst /
Reaction front end and here's the basic explanation of my issue:

Table vhosts has a store of all my DJabbed vhosts and they all have a
numeric PK (i know the name could be a natural key, but i like numeric
IDs sometimes)

My vhost_bridges table has two fields vhost_1_id and vhost2_id which
represent vhosts which are allowed to talk to each other. Because I am
only dealing with two way relationships there is no difference between
vhost1 and vhost2. In order to fetch all bridged hosts I am using the
following sub in lieu of a relation. The rest of the code follows.
what does everyone think and can anyone give me a better way to deal
with this?

#the project JUST got started so there's not much code, but
#what is done of the code is available at:
http://code.google.com/p/cantella-djabberd/source/browse/trunk/lib/Cantella/DJabberd/


sub bridged_hosts{
  my $self = shift;
  my $schema = $self->result_source->schema;
  my $rs = $schema->resultset('VhostBridge');
  my $id = $self->id;
  my @ids = grep{ $_ != $id } map{$_->vhost1_id, $_->vhost1_id}
    $rs->search({ -or =>[ vhost1_id => $id,  vhost2_id => $id ]})->all;
  my $result = $schema->resultset('Vhost')->search_rs({id => {-in => \@ids}});
  return @_ > 0 ? $result->search_rs(@_) : $result;
}


#Full code below:

package Cantella::DJabberd::Schema::Vhost;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("vhosts");
__PACKAGE__->add_columns
  (
   id       => {
                data_type => "INT",
                size => 10,
                default_value => undef,
                is_nullable => 0,
                is_auto_increment => 1,
                extra => { unsigned => 1 },
               },
   hostname => {
                data_type => "VARCHAR",
                size => 255,
                default_value => "",
                is_nullable => 0,
               },
  );

__PACKAGE__->set_primary_key("id");
__PACKAGE__->add_unique_constraint(hostname => ["hostname"]);

__PACKAGE__->has_many
  (
   external_components => "Cantella::DJabberd::Schema::ExternalComponent",
   { "foreign.vhost_id" => "self.id" },
  );

sub bridged_hosts{
  my $self = shift;
  my $schema = $self->result_source->schema;
  my $rs = $schema->resultset('VhostBridge');
  my $id = $self->id;
  my @ids = grep{ $_ != $id } map{$_->vhost1_id, $_->vhost1_id}
    $rs->search({ -or =>[ vhost1_id => $id,  vhost2_id => $id ]})->all;
  my $result = $schema->resultset('Vhost')->search_rs({id => {-in => \@ids}});
  return @_ > 0 ? $result->search_rs(@_) : $result;
}


1;


package Cantella::DJabberd::Schema::VhostBridge;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("vhost_bridges");
__PACKAGE__->add_columns
  (
   vhost1_id => {
                 data_type => "INT",
                 size => 10,
                 default_value => "",
                 is_nullable => 0,
                 extra => { unsigned => 1 },
                 is_foreign_key => 1,
                },
   vhost2_id => {
                 data_type => "INT",
                 size => 10,
                 default_value => "",
                 is_nullable => 0,
                 extra => { unsigned => 1 },
                 is_foreign_key => 1,
                },
  );

__PACKAGE__->set_primary_key("vhost1_id", "vhost2_id");

--Guillermo Roditi (groditi)