On 23 Oct 2008, at 10:31, Drew Taylor wrote:
On Fri, Oct 17, 2008 at 4:17 PM, Yves Räber <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] ]
[ - Comments in this message are my own and not ITO opinion/policy - ]
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
