Hi list,
as I'm turning around in circles and not finding a solution that
satisfies me I'm asking here for help:
My schema includes devices, interfaces and lines which can be affected
by one or more maintenances.
A maintenance has a datetime_start and datetime_end column.
device->has_many('rel_maintenance_device')->belongs_to('rel_maintenance')
What I want to achieve is to get a list of maintenances currently active
(datetime_start <= DateTime->now && datetime_end >= DateTime->now ).
My current solution is a virtual DBIC view which joins rel_maintenance
and filters on datetime_start and datetime_end. This has the
disadvantage that I have to pass DateTime->now formatted for the
currently connected database and can't just prefetch the rel.
The actual resultset method looks like this:
=item with_currently_active_maintenances
Returns a resultset including the active maintenances affecting the devices.
=cut
sub with_currently_active_maintenances {
my $self = shift;
# get current day abbreviation
my $dt_now_sql = $self->result_source->storage->datetime_parser
->format_datetime(DateTime->now);
return $self->search_rs(undef, {
bind => [ $dt_now_sql, $dt_now_sql ],
join => { 'rel_currently_active_maintenances' =>
'rel_maintenance' },
});
}
This does work including prefetching but fails when I have a single
$device row object and want to determine whether it currently is in
maintenance or not.
My next try was to add an additional relationship to the link table
which only returns currently active maintenances:
__PACKAGE__->might_have(
'rel_currently_active_maintenance',
'NAC::Model::DBIC::Table::Maintenance',
sub {
my $args = shift;
my $dt_now_sql =
$args->{self_resultsource}->storage->datetime_parser
->format_datetime(DateTime->now);
return {
"$args->{foreign_alias}.id_maintenance" => { -ident =>
"$args->{self_alias}.fk_maintenance" },
"$args->{foreign_alias}.datetime_start" => { '<=' =>
$dt_now_sql },
"$args->{foreign_alias}.datetime_end" => { '>=' =>
$dt_now_sql },
};
},
);
The advantage is that a simple
prefetch => { rel_maintenance_device => 'rel_currently_active_maintenance' }
works and a many_to_many helper defined over those two rels also works
perfectly.
The downside is that the link table rows aren't filtered so
rel_maintenance_device still returns all maintenances ever assigned to
the device.
I've also looked at DBIx::Class::ParameterizedJoinHack, which isn't
necessary as the calling code doesn't need to pass a parameter to the
rel as 'now' can and should be passed automatically.
Am I overlooking another possibility?
Being able to prefetch the data is essential both for performance as for
practical reasons as I have code based on
Catalyst::Controller::DBIC::API which serializes everything returned by
a resultset to JSON which saves me to handcraft the Perl data structure
for each action.
From a high level want I want is a
device->has_many('rel_currently_active_maintenances'), not a
many_to_many helper returning that.
Ideas?
Thanks, Alex
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be
privileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
