and it generates a query like$schema->resultset("Thing")->search({ ... })->prefetch('last_event')-> +all
and then you get Thing row objects where you can say $row->last_event and have a full row object of ThingEvent to work with. It's also fairly fast. Maybe not for selecting every single max event in the whole database, but we were usually restricting the Thing collection to a few thousand that we cared about for the specific query. Of course, you also need an index on thing_event(thing_id,timestamp) for it to be fast. Maybe also thing_event(event_type,thing_id,timestamp).SELECT me.*, thing_max_event.* FROM thing me LEFT JOIN thing_event thing_max_event ON thing_max_event.id = ( SELECT TOP 1 id FROM thing_event WHERE thing_event.thing_id = me.id ORDER BY timestamp DESC ) WHERE ...
Start by adding a helper to the ThingEvent resultset class:
package App::Schema::ResultSet::ThingEvent; sub last_rs { my $self = shift; return $self->search_rs(undef,{ order_by => { -desc => $self->me . 'timestamp' }, rows => 1, # per conversation with ribasushi, this should be safe for a sin +gle row query unsafe_subselect_ok => 1 }); }
Now add a dynamically generated relationship to Thing:
package App::Schema::Result::Thing; __PACKAGE__->might_have( last_event => 'MyApp::Schema::Result::ThingEv +ent', sub { my ($rsrc, $us, $them)= @{$_[0]}{'self_resultsource','self_alias',' +foreign_alias'}; my $guard= $rsrc->schema->set_limit_dialect_scope_guard('Top'); # u +se "SELECT TOP x FROM ..." syntax for subquery return { "$them.id" => { '=' => $rsrc->schema->resultset('ThingEvent')->alias('thing_max_even +t')->search({ 'thing_max_event.thing_id' => { -ident => "$us.id" }, })->last_rs->columns(['thing_max_event.id'])->as_query } }; });
Then you also need that helper that switches to the 'TOP N' limit syntax:
package App::Schema; =head2 set_limit_dialect_scope_guard { my $dialect_scope= $schema->set_limit_dialect_scope_guard('Top'); # make DBIC searches with "SELECT TOP $n" syntax ... } # back to normal By default, DBIC generates ugly nested queries with a row_id range sel +ected in the outer where clause. This is actually the canonical way to do it o +n SQL Server, but can create all sorts of trouble with namespace between + the different layers of the query. Sometimes for sub-queries it is nice t +o have DBIC generate "SELECT TOP 1 foo FROM bar" instead of the usual mess. +DBIC has a C<< sql_maker->limit_dialect >> option for this, but there is no way + to constrain it to a single statement. This "scope guard" object allows you to control the exact scope in whi +ch you want DBIC to use the alternate dialect without worrying about exceptio +ns. =cut # This scope guard object sets the "limit_dialect" of the storage engi +ne back to whatever # it was originally when the object goes out of scope. {package MSSQL_LimitDialectFlipper; sub new { my ($class, $storage, $value)= @_; my $self= { storage => $storage, prev_value => $storage->sql_mak +er->limit_dialect }; Scalar::Util::weaken($self->{storage}); $storage->sql_maker->limit_dialect($value); bless $self, $class } sub DESTROY { $_[0]{storage}->sql_maker->limit_dialect($_[0]{prev_value}) if $ +_[0]{storage}; } } sub set_limit_dialect_scope_guard { my ($self, $new_dialect_value)= @_; MSSQL_LimitDialectFlipper->new($self->storage, $new_dialect_value); }
There are also a number of sugar methods littered throughout this; we had some fun with our ResultSet base class. Let me know if you need those recipes.
Once you get one working, you can make lots of other fun relationships like
$schema->resultset("Thing")->join('last_event_of_type1','last_event_of +_type2') ->where('last_event_of_type1.timestamp' => { '>=', $cutoff1 }, 'last_event_of_type2.timestamp' => { '<', $cutoff2 });
In reply to Re^2: MAX and GROUP BY in DBIx::Class
by NERDVANA
in thread MAX and GROUP BY in DBIx::Class
by Anonymous Monk
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |