Left joins with static conditions DBIx::Class

Okay I have a generic table called attributes, which I would like to store attributes for several different tables. The number of required attributes and the number of tables requiring attributes is not yet know but as iterations of the application occur it's perceived that more tables will require more forms of attributes. The intention is that we can handle the attributes generically until such a time that a refractor / schema change becomes the more sensible solution.

So the generic table looks something like:

Attributes
att_tgt_type
att_tgt_id
att_type
att_value

and a table requiring such attributes maybe the books table:

books
bok_id
bok_title

The books table could have a paperback colour and or a hardback colour which could be stored as follows:

Attributes	paper colour	hardback colour
att_tgt_type	'bok'
att_tgt_id	fk book.bok_id
att_type	'p_colour'	'h_colour'
att_value	'blue'	'green'

Using DBIx::Class to retrieve rows

Given a Schema classes for the attributes and book tables:

package MyApp::Schema::Attributes;

use strict;
use warnings;
use base 'DBIx::Class';

__PACKAGE__->load_components( "PK::Auto", "Core");
__PACKAGE__->table("attributes");

1;

and

package MyApp::Schema::Books;

use strict;
use warnings;
use base 'DBIx::Class';

__PACKAGE__->load_components( "PK::Auto", "Core");
__PACKAGE__->table("books");

We would have access to search the books objects via the usual methods, but how do we retrieve the attributes (if they exist). the DBIx::Class way would be to setup a relation ship in the MyApp::Schema::Book object:

__PACKAGE__->has_many('attributes'=>'MyApp::Schema::Attributes','att_tgt_id');

Though to use this relation ship we must then know a little about our model because we still need to supply the where clauses.

To avoid having to do this in our controller we can encapsulate the model logic closer to the model by defining our own results set such that we can then provide subroutines that will return correctly joined queries for the attributes as result sets, so later on when we need to retractor we 'hopefully' only need to modify things at the model level.

In the same file as our MyApp::Schema::Books; we also add the package:

package MyApp::ResultSet::Books;

use strict;
use warnings;
use base 'DBIx::Class::ResultSet';

sub include_pcolours {
my ($self) = @_;

return $self->search(undef,
{from =>[
{me=>'activities'},
[{atts=>'attributes',-join_type=>'left'},
{'atts.att_tgt_type'=>'"bok"','atts.att_type'=>'"p_colour"'},
],
],
'+select'=>'atts.att_value','+as'=>'att_value'}
);
}

sub include_hcolours {
my ($self) = @_;

return $self->search(undef,
{from =>[
{me=>'activities'},
[{atts=>'attributes',-join_type=>'left'},
{'atts.att_tgt_type'=>'"bok"','atts.att_type'=>'"h_colour"'},
],
],
'+select'=>'atts.att_value','+as'=>'att_value'}
);
}
1;

Then at the end of the the MyApp::Schema::Book section add the line:

__PACKAGE__->resultset_class('MyApp::ResultSet::Books');

We can retrieve the (possible) colours of the books eg include any paper back colours

@paperbacks = MyApp::DBIC->schema()->resultset('Books')->include_pcolours()->search({});

Our final books class look like:

package MyApp::Schema::Books;

use strict;
use warnings;
use base 'DBIx::Class';

__PACKAGE__->load_components( "PK::Auto", "Core");
__PACKAGE__->table("books");

__PACKAGE__->resultset_class('MyApp::ResultSet::Books');

1;

package MyApp::ResultSet::Books;

use strict;
use warnings;
use base 'DBIx::Class::ResultSet';

sub include_pcolours {
my ($self) = @_;

return $self->search(undef,
{from =>[
{me=>'activities'},
[{atts=>'attributes',-join_type=>'left'},
{'atts.att_tgt_type'=>'"bok"','atts.att_type'=>'"p_colour"'},
],
],
'+select'=>'atts.att_value','+as'=>'att_value'}
);
}

sub include_hcolours {
my ($self) = @_;

return $self->search(undef,
{from =>[
{me=>'activities'},
[{atts=>'attributes',-join_type=>'left'},
{'atts.att_tgt_type'=>'"bok"','atts.att_type'=>'"h_colour"'},
],
],
'+select'=>'atts.att_value','+as'=>'att_value'}
);
}
1;

and our other classes something like:

package MyApp::DBIC;

use 5.008008;
use strict;
use warnings;

use MyApp::Config;
use MyApp::Schema;

use Carp;
use Config::General;

my $config = MyApp::Config->get('Database');
my $schema = MyApp::Schema->connect(get_DSN(),get_user(),get_password(){AutoCommit=>1},);

# get the database user
sub get_user {
return $config->{user};
}

# get the password for the database user
sub get_password {
return $config->{password};
}

# get the password for the database user
sub get_DSN {
return $config->{DSN};
}

sub schema {
my $self = shift;
return $schema;
}

1;

package MyApp::Config;
use 5.008008;
use strict;
use warnings;
use Carp;
use Config::General;

require Exporter;
our @ISA = qw(Exporter);

our %EXPORT_TAGS = ( 'all' => [ qw() ] );
our @EXPORT_OK = ( @{ $EXPORT_TAGS{'all'} } );
our @EXPORT = qw();
our $VERSION = '0.01';

use constant CONFIG_FILE => './timecards.conf';

sub get ($) {
my ($self, $section) = @_;
my $file = CONFIG_FILE;
my $conf = Config::General->new(
-ConfigFile => $file,
-ExtendedAccess => 1,
);

my %conf;
if ($section) {
if ($conf->exists($section)) {
%conf = $conf->obj($section)->getall();
} else {
return undef;
}
} else {
%conf = $conf->getall();
}
return \%conf;
}
1;

package MyApp::Schema;

use strict;
use warnings;

use base 'DBIx::Class::Schema';

__PACKAGE__->load_classes;

1;