filter many_to_many link table rows on foreign column values

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

filter many_to_many link table rows on foreign column values

Alexander Hartmaier
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: filter many_to_many link table rows on foreign column values

Peter Rabbitson-2
In reply to this post by Alexander Hartmaier
On 03/18/2016 03:20 PM, Hartmaier Alexander wrote:

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

I am still not entirely sure I understand what you are trying to do, but
I think it is this (executable from a DBIC checkout, uses its test schema):

~/devel/dbic$
perl -It/lib -Ilib -MANFANG -MDevel::Dwarn -e '

   # add the special rel
   require DBICTest::Schema::CD;
   DBICTest::Schema::CD->has_many( links_to_non_mst_producers =>
"DBICTest::Schema::CD_to_Producer", sub {
     my $args = shift;
     {
       "$args->{foreign_alias}.cd" => { -ident =>
"$args->{self_alias}.cdid" },
       "$args->{foreign_alias}.producer" => {
         -in => $args->{self_resultsource}
                      ->schema
                       ->resultset("Producer")
                        ->search({ "me.name" => { -not_like => "%Trout%"
} })
                         ->get_column("producerid")
                          ->as_query
       },
     }
   });
 

   require DBICTest;
 

   Dwarn [ DBICTest->init_schema->resultset("CD")->search(
     { cdid => 1 },
     {
       result_class => "DBIx::Class::ResultClass::HashRefInflator",
       prefetch => {
         cd_to_producer => "producer",
         links_to_non_mst_producers => "producer",
       }
     }
   )->all ];
'

This does two parallel prefetches for illustration purposes: one branch
holding all the producers and the other holding just the select producer
list.

Let me know if this is not what you wanted.

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: filter many_to_many link table rows on foreign column values

Peter Rabbitson-2
On 03/30/2016 02:23 PM, Peter Rabbitson wrote:

> perl -It/lib -Ilib -MANFANG -MDevel::Dwarn -e '
>
>    # add the special rel
>    require DBICTest::Schema::CD;
>    DBICTest::Schema::CD->has_many( links_to_non_mst_producers =>
> "DBICTest::Schema::CD_to_Producer", sub {
>      my $args = shift;
>      {
>        "$args->{foreign_alias}.cd" => { -ident =>
> "$args->{self_alias}.cdid" },
>        "$args->{foreign_alias}.producer" => {
>          -in => $args->{self_resultsource}
>                       ->schema
>                        ->resultset("Producer")
>                         ->search({ "me.name" => { -not_like => "%Trout%"
> } })
>                          ->get_column("producerid")
>                           ->as_query
>        },
>      }
>    });
>
>
>    require DBICTest;
>
>
>    Dwarn [ DBICTest->init_schema->resultset("CD")->search(
>      { cdid => 1 },
>      {
>        result_class => "DBIx::Class::ResultClass::HashRefInflator",
>        prefetch => {
>          cd_to_producer => "producer",
>          links_to_non_mst_producers => "producer",
>        }
>      }
>    )->all ];
> '

Forgot to add - this is what the above produces:

[
   {
     artist => 1,
     cd_to_producer => [
       {
         attribute => undef,
         cd => 1,
         producer => {
           name => "Matt S Trout",
           producerid => 1
         }
       },
       {
         attribute => undef,
         cd => 1,
         producer => {
           name => "Bob The Builder",
           producerid => 2
         }
       },
       {
         attribute => undef,
         cd => 1,
         producer => {
           name => "Fred The Phenotype",
           producerid => 3
         }
       }
     ],
     cdid => 1,
     genreid => 1,
     links_to_non_mst_producers => [
       {
         attribute => undef,
         cd => 1,
         producer => {
           name => "Bob The Builder",
           producerid => 2
         }
       },
       {
         attribute => undef,
         cd => 1,
         producer => {
           name => "Fred The Phenotype",
           producerid => 3
         }
       }
     ],
     single_track => undef,
     title => "Spoonful of bees",
     year => 1999
   }
]


_______________________________________________
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@...
Loading...