[Dbix-class] Join Myself?

[Steve Wells]

I'm trying to convert this SQL statement to DBIC and I'm failing miserably.

given:

mytable
------------
id
foreignkey
date_added

SQL
------
select T1.id from mytable T1
   left join mytable T2 on
     (T1.foreignkey = T2.foreignkey
      and
      T1.date_added < T2.date_added)
    where T2.date_added is null
      order by T1.date_added desc;
-----

At first I just tried joining the table normally but I'm stumped as to
how to add a table to itself without changing the Mytable.pm and
setting a relationship back to itself…

I tried adding a belongs_to relationship to Mytable.pm as 'myself' and
link the foreign keys but I seem to only be able to add 'foreignkey =
foreignkey' as it won't accept the AND portion with the < as opposed
to =.

in Mytable.pm
-------
__PACKAGE__->belongs_to(
  "myself",
  "Schema::Result::Mytable",
  { 'foreignkey' => 'foreignkey', 'date_added < date_added' },
  {
    is_deferrable => 1,
    join_type     => "LEFT",
  }
);
--------

I'm either missing something obvious or this can't be done (yet) and I
should just use SQL to make the request.  I guess I just want to know
either way.

TIA,
--
STEVE
-----