my $rs = $schema->resultset('Events')->search(
  {},
  {
    select   => [ 'name', { max => 'event_id' } ],
    as       => [qw/ name max_event_id /],
    group_by => [qw/ name /]
  }
);
[download]

Not sure I got your needs right, as I don't work with DBIx::Class, but is there any reason you can't use a CTE (common table expression) in your query?

Something like (for Postgresql) :

with t1 as (SELECT MAX(event_id) as max_event_id, name FROM events GRO
+UP BY name) 
SELECT * FROM events INNER JOIN t1 ON t1.max_event_id = events.event.i
+d;
[download]

that would give you the list of objects directly

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

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]

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

$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]