[Dbix-class] Restricting Result Sets

[Ovid]

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