[Dbix-class] deleting from a view

[Ashley Pond V]

On Wed, Jul 6, 2011 at 7:01 AM, Dave Howorth <dhoworth at mrc-lmb.cam.ac.uk> wrote:
> I have a DBIC class made up using a UNION, something like this
> simplified example:
>
>  __PACKAGE__->table('nodes');
>  __PACKAGE__->result_source_instance->is_virtual(1);
>  __PACKAGE__->result_source_instance->view_definition(
>   "SELECT
>        sp_id AS id,
>        sp_status  COLLATE latin1_general_ci AS status,
>        FROM sp
>    UNION
>    SELECT
>        pr_id AS id,
>        pr_status  COLLATE latin1_general_ci AS status,
>        FROM pr");
>
> It works fine but I now find I need to delete all records from the
> database where status = 'obsolete'.
>
> I've discovered that this doesn't work:
>
>  $node_rs->search({status => 'obsolete' })->delete;
>
> and neither does this:
>
>  my $obs_node_rs = $node_rs->search({status => 'obsolete' });
>  while (my $node = $obs_node_rs->next) {
>     $node->delete;
>  }
>
> Is there some idiom that does work, or am I going to need to operate on
> the individual tables separately (there are more than two, sadly)?
>

Untested, stub code, YMMV, might need to handle two real tables/RSes,
et cetera, but something like this will work if adapted to your code-

my $sp_id_query = $node_rs->search({status => 'obsolete' })
    ->get_column("sp_id")
    ->as_query;

$sp_rs->search({ id => { IN => $sp_id_query } })
    ->delete_all;