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

[Peter Rabbitson]

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.