[Dbix-class] filter many_to_many link table rows on foreign column values

[Marco]

Excerpts from Hartmaier Alexander's message of 2016-03-18 15:20:54 +0100:
> 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')
You mean:
device->has_many('rel_maintenance_device')
rel_maintenance_device->belongs_to('rel_maintenance') ?

It seems a poor schema...

You really have a **many_to_many** relationship between
(devices/interfaces/lines) and scheduled maintenances.

> 
> 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.

You mean an instance of device. It is not a fail, it is because a
ResultSet is not a Result Class, these are different animals.

> 
> 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.

Again you don't understand the difference between a ResultSet and the
instance of a ResultClass
> 
> 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?

Create a DBIC::Scheduler that is based on a view with the SQL:

SELECT whatever FROM maintenance WHERE datetime_start >= NOW AND
datetime_end <= NOW 

and so you can join/prefetch/ everything else...

> 
> 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.
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> 

-- 
Marco Arthur @ (M)arco Creatives