[Dbix-class] Filtering out inactive records

[Vladimir Melnik]

Wow, thank you very much! It works great.

Do you have any suggestions on how to filter out the "inactive" records from the "actor_x_movie" table?

Thanks!

On Thu, Feb 09, 2017 at 12:05:29PM +0000, Colin Newell wrote:
> The general ambiguity problem is easily solvable, what you need is the
> 'current_source_alias'.  That allows you to disambiguate your field
> references and it works perfectly in the chained searches.
> 
> http://search.cpan.org/~ribasushi/DBIx-Class-0.082840/lib/DBIx/Class/ResultSet.pm#current_source_alias
> 
> 
> Colin.
> 
> On 9 February 2017 at 11:55, Vladimir Melnik <v.melnik at uplink.ua> wrote:
> > Hello,
> >
> > Lots of thanks to the founders and maintainers for such a useful and handy framework! :)
> >
> > The database schema of the project I'm currently working on implies presence of 3 additional fields in each table:
> >   `valid_since` datetime NOT NULL,
> >   `valid_till` datetime DEFAULT NULL,
> >   `removed` datetime DEFAULT NULL
> >
> > Their names are pretty self-descriptive: most of queries (not all, but most of them) should select only the rows that are "valid" (valid_since <= NOW() AND valid_till > NOW()) and aren't "removed" (removed IS NULL).
> >
> > I decided to create a package that is called ***::Schema::DefaultResultSet with the following method:
> >
> >     method filter_valid (
> >         DateTime $now? = DateTime->now
> >     ) {
> >         $self->search(
> >             {
> >                 -and => [
> >                     { removed       => { '='    => undef } },
> >                     { valid_since   => { '<='   => $now } },
> >                     {
> >                         -or => [
> >                             { valid_till    => { '=' => undef } },
> >                             { valid_till    => { '>' => $now } }
> >                         ]
> >                     }
> >                 ]
> >             }
> >         );
> >     }
> >
> > It lets me to do call the filter_valid method for filtering out the "disabled" records for each result. For example:
> >     $db_schema->resultset("Actor")->search({ name => 'Vasya' })->filter_valid;
> >
> > It works fine until I'm not trying to implement it for many-to-many relations. There are 2 problems I'm facing.
> >
> > (1) "Column 'valid_till' in where clause is ambiguous"
> >
> > There's the "actor_x_movie" table which is being used for building the many-to-many relation between the "actor" and "movie" tables. In the ***::Schema::Result::Actor I've added the following:
> >
> >     __PACKAGE__->many_to_many("movies" => "actor_x_movie", "movie");
> >
> > It works fine, so I can do the following:
> >     my $actor = $db_schema->resultset("Actor")->search({ name => 'Vasya' })->filter_valid->first;
> >     my $movie = $actor->movie->first;
> > ...and it gives me the first result from the "movie" table that the first result of the "actor" table is related to. That's fine.
> >
> > The problem appears when I add the filter_valid:
> >     my $movie = $actor->movie->filter_valid->first;
> >
> > I'm getting the following exception:
> > DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Column 'valid_till' in where clause is ambiguous [for Statement "SELECT movie.id, movie.valid_since, movie.valid_till, movie.removed, movie.name, movie.movie_type_id, movie.provider FROM actor_x_movie me  JOIN movie movie ON movie.id = me.movie_id WHERE ( ( ( valid_till IS NULL OR valid_till > ? ) AND me.actor_id = ? AND removed IS NULL AND valid_since <= ? ) )" with ParamValues: 0='2017-02-08 12:38:02', 1=2, 2='2017-02-08 12:38:02']
> >
> > Is there a way to make DBIx::Class performing the "filter_valid" part _after_ performing the "movie" query? Without something like that:
> >     $my $movie = $actor->movie; $movie = $movie->filter_valid->first?
> >
> > (2) Filtering out the "disabled" records from the "actor_x_movie" table
> >
> > Can I make DBIx::Class calling the "filter_valid" method for the records of "actor_x_movie" too? Of corse, I can do something like that:
> >     __PACKAGE__->many_to_many("movies" => "actor_x_movie", "movie", {
> >         -and => [
> >             { removed       => { '='    => undef } },
> >             { valid_since   => { '<='   => \'NOW()' } },
> >             {
> >                 -or => [
> >                     { valid_till    => { '=' => undef } },
> >                     { valid_till    => { '>' => \'NOW()' } }
> >                 ]
> >             }
> >         ]
> >     } );
> > ...but adding it for EACH many-to-many relation doesn't seem to be a good idea. :( Is there a way to call "filter_valid" for the "actor_x_movie" resultset when performing the many-to-many query?
> >
> > I'm considering using MySQL Views. Maybe I need to make a separate view for each table: valid_actor, valid_movie, valid_actor_x_movie and so on, but it won't be tidy. :( What would you suggest? Did you have to deal with similar problems? What practices are the best? I'll be very grateful for any hints and tips regarding this topic.
> >
> > Thanks!
> >
> > --
> > V.Melnik
> >
> > _______________________________________________
> > 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
> 
> _______________________________________________
> 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

-- 
V.Melnik