[Dbix-class] updating multiple records at once

[Jonathan Rockway]

"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.

Regards,
Jonathan Rockway