Filtering out inactive records

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Filtering out inactive records

Vladimir Melnik
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Filtering out inactive records

Colin Newell
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 <[hidden email]> 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@...

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

Re: 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 <[hidden email]> 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@...
>
> _______________________________________________
> 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@...

--
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Filtering out inactive records

Dmitry Latin
You can pass source alias to filter_valid like
my $movie =
$actor->movie->filter_valid->first;
    ->filter_valid # for me
    ->filter_valid()

But I'd prefer views

On 9 February 2017 at 15:17, Vladimir Melnik <[hidden email]> wrote:

> 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 <[hidden email]> 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@...
>>
>> _______________________________________________
>> 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@...
>
> --
> 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@...



--
//wbr, Dmitry L.

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

Re: Filtering out inactive records

Dmitry Latin
Sorry.

my $movie = $actor->movie
    ->filter_valid()               # for actor_x_movie
    ->filter_valid('movie')    # for movie
    ->first;

On 9 February 2017 at 15:28, Dmitry L. <[hidden email]> wrote:

> You can pass source alias to filter_valid like
> my $movie =
> $actor->movie->filter_valid->first;
>     ->filter_valid # for me
>     ->filter_valid()
>
> But I'd prefer views
>
> On 9 February 2017 at 15:17, Vladimir Melnik <[hidden email]> wrote:
>> 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 <[hidden email]> 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@...
>>>
>>> _______________________________________________
>>> 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@...
>>
>> --
>> 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@...
>
>
>
> --
> //wbr, Dmitry L.



--
//wbr, Dmitry L.

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

Re: Filtering out inactive records

Vladimir Melnik
Yes, that seems to be working fine, thank you very much!

foreach my $movie ($actor->movies->filter_valid(source_alias => 'me')->filter_valid(source_alias => 'movie')) { ... }

Makes:

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 ( ( ( ( movie.valid_till IS NULL OR movie.valid_till > ? ) AND ( me.valid_till > ? OR me.valid_till IS NULL ) ) AND movie.removed IS NULL AND movie.valid_since <= ? AND me.actor_id = ? AND me.removed IS NULL AND me.valid_since <= ? ) ): '2017-02-08 14:15:21', '2017-02-08 14:15:21', '2017-02-08 14:15:21', '2', '2017-02-08 14:15:21'

Thanks a lot!

On Thu, Feb 09, 2017 at 03:29:23PM +0300, Dmitry L. wrote:

> Sorry.
>
> my $movie = $actor->movie
>     ->filter_valid()               # for actor_x_movie
>     ->filter_valid('movie')    # for movie
>     ->first;
>
> On 9 February 2017 at 15:28, Dmitry L. <[hidden email]> wrote:
> > You can pass source alias to filter_valid like
> > my $movie =
> > $actor->movie->filter_valid->first;
> >     ->filter_valid # for me
> >     ->filter_valid()
> >
> > But I'd prefer views
> >
> > On 9 February 2017 at 15:17, Vladimir Melnik <[hidden email]> wrote:
> >> 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 <[hidden email]> 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@...
> >>>
> >>> _______________________________________________
> >>> 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@...
> >>
> >> --
> >> 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@...
> >
> >
> >
> > --
> > //wbr, Dmitry L.
>
>
>
> --
> //wbr, Dmitry L.
>
> _______________________________________________
> 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@...

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