[Dbix-class] Restricting Result Sets

[Jess Robinson]

On Thu, 6 Nov 2008, Ovid wrote:

> Short question (I hope).  I have a resultset which inflates objects based upon an id.  While the ID may exist, it's acceptable for the underlying inflated object to not exist.  Unfortunately, this means that the "$rs->count" might report more objects than actually exist.
>
> This is the correct behavior, but we'd like to be able to create a resultset with a filter applied which represents what we really have.  Something like:
>
>  my $new_rs = $rs->filter( sub { defined $_->inflate_entity  } );
>
> '$rs->search_rs' doesn't seem appropriate because regrettably, joining across tables doesn't appear to be an option here.
>
> Cheers,
> Ovid
>
> Longer description below for those who are confused.
>
> Note:  This is a greatly simplified description of the problem, reducing it to its core elements.
>
> Customers require all objects to be searchable via a common ID formats.  So let's say we have three tables:
>
>  brand
>  series
>  episodes
>
> And we have an 'identifier' table (again, greatly simplified):
>
>  identifier
>  ----------
>  identifier_id int primary key
>  identifier    varchar(32)
>  object_type   enum('Brand', 'Series', 'Episode')  (yuck)
>
> So if a customer gives me an id, I can check in the identifier table and see that it's a key for a brand and this requires a second check in the brand table to fetch the item.  Here's the problem:  if we delete a brand, series or episode, we cannot delete the identifier because it must not be reused.  Though some of these are custom methods, you should be able to understand the problem from here:
>
>    my $entity = $schema->resultset('Identifier')->search(
>        { type => 'pid' }
>    )->first->inflate_entity;
>
>    my $pid = $entity->pid;
>
>    $entity->delete;
>
>    my $rs = $schema->resultset('Identifier')->search({
>        type  => 'pid',
>        value => $pid,
>    });
>    print $rs->count;  # prints 1, even though the $entity is gone


This part of the description makes it sound like your constraints/filter 
criteria are all SQL-based.. That essentially you need a more complex 
search that ends up similar to (psuedo sql):

select * from Identifier
where type='pid' and value=$pid
    and (exists select pid from brand
         or exists select pid from series
         or exists select pid from episodes)

- which will only return used ones.

As for doing this on an rs where the condition is NOT database based, all 
I can think of is: add cache => 1 to the attrs, force the rs to fill the 
cache by calling ->all on it or something, then post-manipulate the cache 
contents on the $rs object.

->count doesnt use the cache that I know of, tho one could override it to.
other methods should, and if you used prefetch as well it should happily 
assume your manipulated cache is correct.

see set_cache/get_cache on ResultSet.

btw your inflate_entity sounds a lot like inflate_result, see 
::Manual::Cookbook for an example.

Jess