[Dbix-class] trouble with using 'select' in a search clause

[Toby Corkindale]

On Thu, May 18, 2006 at 02:56:00PM +0100, Matt S Trout wrote:
> Toby Corkindale wrote:
> >> ... but if you can't persuade it to run something that's at least *almost* 
> >> equivalent to the pure hand-crafted SQL you would have done anyway, I want to 
> >> know what the difference is so I can hack it in as a supported feature.
> > 
> > Hell, you probably already have :)
> > 
> > Here's an example:
> > Say you want to increment a column in a bunch of records, eg. here's a
> > synthetic example in SQL:
> > CREATE TABLE example ( id SERIAL PRIMARY KEY, ..., foo INTEGER );
> > UPDATE example SET foo = foo + 1 WHERE id < 1000;
> > 
> > With my current (incomplete, ungodlike) understanding of D::C, I'd implement
> > that with a ->search (to get all the records with id < 1000), and then loop
> > through them like:
> > while (my $i = $results->next) { $i->foo($i->foo + 1); $i->update; }
> > 
> > Which is going to generate a couple of thousand queries!
> > I could see that it's probably possible to get it down to just over 1000
> > queries if you fiddle with the prefetching or something.. but still..
> > 
> > Also, you might want to be doing this operation inside a transaction - in which
> > case, you'd be blocking other queries for quite a long time, whereas the
> > original SQL example would allow the database to continue to operate on the
> >> 1000 part of the table.
> > 
> > I think... Tell me I'm wrong? :)
> 
> Eek.
> 
> my $rs = $schema->resultset('Example')->search({ id => { '<', 1000 });
>             # remember this doesn't actually run a query
> 
> $schema->txn_do(
>    sub { $rs->update({ foo => \"foo + 1" }); }
> );

Excellent :)

I'm glad I found that out before I had to actually do something like that!

tjc

-- 
Turning and turning in the widening gyre/The falcon cannot hear the falconer;
Things fall apart, the centre cannot hold/Mere anarchy is loosed upon the world
(gpg --keyserver www.co.uk.pgp.net --recv-key B1CCF88E)