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