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/
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
