[Dbix-class] updating multiple records at once

[Aaron Trevena]

On 27/01/2008, Jonathan Rockway <jon at jrock.us> wrote:
> "Aaron Trevena" <aaron.trevena at gmail.com> writes:
>
> > On 27/01/2008, Jonathan Rockway <jon at jrock.us> wrote:
> >> "Aaron Trevena" <aaron.trevena at gmail.com> writes:
> >>
> >> > On 25/01/2008, Jonathan Rockway <jon at jrock.us> wrote:
> >> > Can you do that with subsets of a result set - i.e. I have a resultset
> >> > of a page of records, 20 need to have the "fantastic" flag set, 100
> >> > need to have the state set from "pending" to approved.
> >>
> >> Well, one resultset should contain the records that need to be
> >> "fantastic", and another should contain the records that need to be
> >> approved.  Then you update them as appropriate.
> >
> > What about slices of resultsets - can't I fetch my 100 objects then
> > put 30 in on resultset, 90 in another and 40 in yet another without
> > hitting the database 4 times instead of 3 ?
>
> ResultSets are lazy; the code I posted hits the database twice, once
> for each search-and-update operation.  I doubt you can do better than
> that manually.
>
> Here's an example query I wrote (on a test database, one table called
> Table with id PK/level INT/name TEXT).
>
> I added records like this:
>
>   for my $level (1..10){
>       $schema->resultset('Table')->
>         create({ level => $level, name => "INITIAL"})
>           for 1..10;
>   }
>
> Then ran this query, which is pretty much the same as what I showed
> you earlier:
>
>   $schema->resultset('Table')
>     ->search({ level => { '>' => 2 } })
>     ->search({ level => { '<' => 6 } })
>     ->update({ name => "BETWEEN 2 AND 6 (EXCLUSIVE)" });
>
> The resulting SQL is a single statement, as expected:
>
>   UPDATE table SET name = ?
>   WHERE ( ( ( level < ? ) AND ( level > ? ) ) ):
>    ('BETWEEN 2 AND 6 (EXCLUSIVE)', '6', '2')
>
> It doesn't get much better than this!  The thing to keep in mind is
> that the database is not queried until your perl program actually
> requires data from the database.  That means you can chain things as
> deeply as you like, and the result will usually only be one query.

The ids of the objects will be FKs and provided by the form they
submit, so the nested searching isn't a win - we'll be dealing with
"IN (large list)" a lot, but this should cover it if I provide great
big long lists of ids.

I may try and write a wrapper/trigger to track changes to objects and
then build a couple of update statements instead of 1 per object,
which is what I'm trying to avoid.

The codebase we're refactoring currently does updates per change per
record - so that can be 4 or 5 * 250 per form submission :(

A.

-- 
http://www.aarontrevena.co.uk
LAMP System Integration, Development and Hosting