[Dbix-class] Using update on a resultset with join

[Peter Rabbitson]

Trevor Leffler wrote:
> I'm stuck with (at the moment) version 0.08011 of DBIC, so please let me
> know if a newer version addresses this issue.
> 
> I'm creating a resultset of source 'Foo', joining against one or more
> other sources, and then trying to update, assuming that the update would
> work against 'Foo' and not the joined-in tables.  Here's what I've got:
> 
> package My::Schema::ResultSet::Foo
> ...
> sub mark_as_exported {
>   ...
>   $self->search(
>     {me.exported = 0, bar.state = 'WA'},
>     {join => 'bar'}
>   )->update({exported = 1});
>   ...
> }
> 
> This DBIC tracefile is produced:
> $ less /tmp/trace.out
> UPDATE foo SET exported = ? WHERE ( ( exported = ? AND state = ? AND )
> ): '1', '0', 'WA'
> 
> Whereas I was expecting SQL like:
> UPDATE foo me JOIN ( bars bar ON bars.id = me.bar_id ) SET me.exported =
> 1 WHERE ( me.exported = 0 AND bar.state = 'WA' )
> 

Newer versions address this issue, albeit not with the SQL you proposed
(I am not aware of any RDBMS which will execute the above sanely - i.e.
*really* restrict the update-able set with the join). In any case -
simply upgrade.