[Dbix-class] deleting from a view

[Dave Howorth]

Ashley Pond V wrote:
> 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;

I assumed that $sp_rs = $schema->resultset('Sp'). Given that assumption,
it generates code which deletes all the rows in 'sp', as long as at
least one of them has 'obsolete' status. I'm not sure how to modify it
to just delete the obsolete ones.

I modified my code to look like this:

 my $obs_node_rs = $node_rs->search({status => 'obsolete' });
 while (my $node = $obs_node_rs->next) {
     $node->underlying->delete;
 }

where 'underlying' is a new method I wrote that casts a node to a
corresponding object from the underlying table ('sp' or 'pr' etc).
That works but results in a series of SELECT followed by DELETE:

 SELECT me.sp_id, me.sp_status FROM sp me WHERE ( me.sp_id = ? ): '600011'
 DELETE FROM species WHERE ( sp_id = ? ): '600011'

The SELECTs are completely redundant. Is there any way in DBIC to delete
a row from a table given the primary key other than retrieving a full
object to invoke delete on?

Fortunately, there's only a few thousand rows so a better method is now
more a matter of tidiness than necessity.