[Dbix-class] Deleting with JOIN's

[Nigel Metheringham]

On 23 Oct 2008, at 10:31, Drew Taylor wrote:

> On Fri, Oct 17, 2008 at 4:17 PM, Yves Räber <yraber at mailup.net> wrote:
>> And That's the piece of code that I'm trying to make work :
>>
>> my $extensions = $c->model('MyApp::Batch')->search(
>> {
>> state => ['STOPPED', 'ARCHIVED', 'DONE']
>> }
>> )->search_related('script')->search_related('astextensions');
>>
>> Now if I try $extensions->delete , this is what I get (I have to  
>> admit I
>> don't really understand why I get this):
>
> I'm not a DBIC guru by any means, but I ran into a situation like this
> recently where all the chained calls weren't being followed and the
> solution was to run all methods (search->search->search in my case) in
> a single call. Maybe try:
> ...->search_related('script')->search_related('astextensions')- 
> >delete;


Strangely we were having a discussion about this on irc yesterday.

The bottom line is basically a DELETE that involves a join will not  
generate correct SQL.
This is at least partly down to the SQL spec not really supporting  
that sort of complexity in a DELETE statement, although some  
implementations of SQL (I think PostgreSQL is one)  will do so,  
however the SQL generator does not generate anything sensible in this  
case.

A way around this would be to use a subselect, except DBIC doesn't  
support this well at present (you can force it by putting literal SQL  
in your query, but you are on your own here).

I transformed a query into another form something like:-
   $schema->resultset('Target')->search({thing =>
      {-in => [ $schema->resultset('Source')->search(
        {seomthing => 'whatever'})->get_column('id')->all]}});

However that made the db explode when the IN clause generated had more  
than 15,000 entries :-)

The other method you can use, that should work everywhere, is to use  
delete_all in place of delete, but this pulls all the objects back  
into perl which is a pain.

Anyhow, suggested changes for now were:-
   - add additional bits to documentation for ResultSet delete and
     delete_all to explain the problem

   - modify delete to either die or warn when it is applied to a
     rs with joins in it.

If anyone has good wording for the doc changes in particular I can  
look at making these changes.

	Nigel.
--
[ Nigel Metheringham             Nigel.Metheringham at InTechnology.com ]
[ - Comments in this message are my own and not ITO opinion/policy - ]