Having fought through nearly that exact situation for SQL Server 2008 (which lacks most modern SQL features) a number of years back, I dug out this code and anonymized it a bit. The end result is that you say

$schema->resultset("Thing")->search({ ... })->prefetch('last_event')->
+all
[download]

and it generates a query like

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 ...
[download]

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

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
   });
}
[download]

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
      }
   };
});
[download]

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);
}
[download]

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

• last_event_of_type1
• first_event_of_type1
• last_event_of_type2
• first_event_of_type2

and then make all sorts of fun queries 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 });
[download]