[Dbix-class] Restricting Result Sets

[Zbigniew Lukasiak]

On Thu, Nov 6, 2008 at 4:56 PM, Ovid <publiustemp-dbic at yahoo.com> 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

How about adding a column that would say if a given pid is deleted to
the Identifier table?  Then
    my $rs = $schema->resultset('Identifier')->search({
        type  => 'pid',
        value => $pid,
        is_deleted => 0,
    });
    print $rs->count;  # prints 0

No joins - just a bit denormalized.

-- 
Zbigniew Lukasiak
http://brudnopis.blogspot.com/
http://perlalchemy.blogspot.com/