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