[Dbix-class] how to make this relationship?

[Ryan VanderBijl]

Hello,

Lets say I have a gps device table, and a positions table looking something
like this:
     CREATE TABLE gpsdev ( dev_id bigserial primary key );
     CREATE TABLE positions (                             
          pos_id bigserial primary key,
          dev_id bigint not null references gpsdev(dev_id),
          lat float not null,
          lng float not null,
          dt timestamp
     );               

I have my schema and my schema::gpsdev, schema::positions classes working
fine. I even have a 'positions' relationship set up and working:
    __PACKAGE__->has_many(                                      
        "positions",
        "schema::positions",
        { "foreign.dev_id" => "self.dev_id" },
        { "order_by" => [ "dt desc" ] },
    );                                  

I want to add a 'might_have' relationship to the gpsdev class that does
the equivelant of:
    sub current_position {
        my $self = shift;
        return $self->positions->first;
    }

That is to say: a way to get the most recent position for a given
device.

Any suggestions on how to set this up?  Here is an SQL statement that
should basically do what I want:
    SELECT positions.*          
      FROM positions  
           INNER JOIN
               (SELECT mti.dev_id, MAX(mti.dt)
                  FROM positions AS mti
                GROUP BY mti.dev_id) AS mt
           ON positions.dev_id = mt.dev_id AND
              positions.dt = mt.dt
    WHERE positions.dev_id = ?


The above names are made-up, not from copy and paste. So please be kind
if I typed something wrong.

Thanks for your help!

-Ryan

-- 
Ryan VanderBijl   |   http://vbijl.net/~ryan/