[Dbix-class] Fun with auto-restricted result sets

[fREW Schmidt]

>
> David Ihnen wrote:
>>>
>>>> So in the wake of fREW showing a modification of the classes to allow
>>>> the modification of the delete, I utilized a corrollary concept...
>>>> restricting a result set automatically/transparently, as you would in =
case
>>>> of that delete - not normally showing the deleted rows.  In my case, my
>>>> alerts table has a possible action window, possibly has been dismissed=
, and
>>>> possibly might be inactive.  This made the resultset class more compli=
cated,
>>>> but not unmanageable. It was fun to work through the logic of modifyin=
g an
>>>> arbitrarily complex requested search clause.  If you specify values for
>>>> these, it is assumed you know what you're doing and this result set cl=
ass
>>>> won't further modify the terms.  I may have missed the handling of alr=
eady
>>>> defined alert_expire terms though... maybe I should make it inactive i=
f you
>>>> specify any of the terms anywhere in the \%where tree.
>>>>
>>>>
>>>> package DB::Schema::active_alert_resultset;
>>>> use base 'DBIx::Class::ResultSet';
>>>>
>>>> sub search {
>>>>   my $self =3D shift;
>>>>   $_[0]->{'inactive'}     ||=3D 0;
>>>>   $_[0]->{'dismissed'}    ||=3D \"IS NULL";
>>>>   $_[0]->{'alert_time'}   ||=3D { '<' =3D> \"NOW()" };
>>>>   my $aeor =3D [];
>>>>   push @{$aeor}, { alert_expire =3D> { '>' =3D> \'NOW()'} };
>>>>   push @{$aeor}, { alert_expire =3D> \'IS NULL' };
>>>>   if ($_[0]->{'-or'}) {  # If there is already an -or, we need to nest
>>>> it into an -and
>>>>                          # so we don't overwrite the term, or change t=
he
>>>> logic by orring with it.
>>>>     my $and =3D $_[0]->{'-and'} ||=3D [];  # Use an existing -and term=
 if
>>>> already supplied.
>>>>     push @{$and}, { -or =3D> delete $_[0]->{'-or'} }; # Move the old -=
and
>>>>     push @{$and}, { -or =3D> $aeor }; # add our alert expire or term
>>>>   } else {
>>>>     $_[0]->{'-or'} =3D $aeor;
>>>>   }
>>>>   return $self->next::method( @_ );
>>>> }
>>>>
>>>
>>> You're only checking to see if the given columns have specified values
>>> passed in, but what if ->search was called on a resultset object that
>>> *already* has constraints for these columns?
>>>
>> What?  How can you be in a result class and not in it too?  Can you give
>> an example of how to write that in code?
>>
>>
>>  Until DBIC goes Moose, it's tricky (and not very safe) to introspect an
>>> existing RS to see what's already been done to it...
>>>
>> If I have already restricted the resultset, then re-restricting it by the
>> same terms will filter out whats not there, right?  And asking for dismi=
ssed
>> =3D> \'IS NOT NULL' on an alerts (or derived) resultset will always give=
 you
>> empty set.  No active alerts have dismissed NOT NULL
>>
>
> The problem comes when you try to "derive" one resultset from another; any
> criteria not present are clobbered by your default assumptions. Let's say
> that one part of your code retrieves inactive alerts:
>
>  my $rs =3D $schema->resultset('alert')->search({inactive =3D> 1});
>
> Then another part of your code wants to take that same result set and lim=
it
> it to just those alerts from last year:
>
>  my $new_rs =3D $rs->search({alert_time =3D> {'<' =3D> \'current_yr()'}});
>  # replacing 'current_yr()' with the appropriate syntax for your dbms
>
> Using your methodology, this invocation of ->search will set 'inactive'
> back to '0', because it's not passed in.
>
> Of course, if you only call search once with both criteria, you don't have
> a problem. But I've been finding the additive technique (sometimes referr=
ed
> to as "resultset chaining") to make for very clean, readable, maintainable
> code. In fact, you could use such an approach as an alternative to
> overriding ->search with your defaults:
>
>  package DB::ResultSet::alerts;
>  use base 'DBIx::Class::ResultSet';
>
>  sub active {
>      return shift->search({
>          inactive   =3D> 0,
>          dismissed  =3D> \'is null',
>          alert_time =3D> { '<' =3D> \"NOW()" }
>          '-or' =3D> [
>              { alert_expire =3D> { '>' =3D> \'NOW()'} },
>              { alert_expire =3D> \'IS NULL' },
>          ],
>      });
>  }
>
> Then anywhere in your code that you want to enforce "activeness", just do:
>
>  my $rs =3D $schema->resultset('alerts')->active->search({foo =3D> 'bar'}=
);
>
> It's not *quite* as slick as having it defaulted automatically, but it
> protects you from unwittingly clobbering previously set criteria.


>From IRC conversations it appears that really the slickest way to do these
things is to define a ResultSource (not result set) as that can help you
with joins and whatnot as well.  I plan on using this for my Paranoid
Deletion setup, but I can't *just* work on the DB side at work, so it will
have to wait a bit :-)
-- =

fREW Schmidt
http://blog.afoolishmanifesto.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090220/e9c=
4025e/attachment.htm