Find the Latest in a Set with Propel 1.5

Question

Similar to the question here, I am attempting to get the latest result for a given set of items. So, if a machine has a history of where it's been, I am trying to find the latest place:

machine:
  id: ~

machine_history:
  machine_id:
    type: integer
    foreignTable: machine
  location_id:
    type: integer
    foreignTable: location
  time:
    type: timestamp
    required: true

I have adapted the SQL from the linked question like this:

SELECT l1.* FROM machine_history l1
LEFT JOIN machine_history l2 ON
  (l1.machine_id = l2.machine_id AND l1.time < l2.time)
WHERE l2.id IS NULL;

This does as expected, but I would like to transform this into a Propel 1.5 Query. As I do not know how to perform joins with multiple criteria, I am resorting to Criteria's addJoin(). Unfortunately, it's not doing what I would like, and I don't know how to use it properly. So far I have written this:

return $this
  ->addJoin(
    array(MachineLocationPeer::ID, MachineLocationPeer::TIME),
    array(MachineLocationPeer::ID, MachineLocationPeer::TIME),
    Criteria::LEFT_JOIN
  )
  ->filterById(null);

I don't know how to specify the comparison to use for each of the criteria. Nor do I know how to use an alias so that I can successfully join the entity with itself. How might I do this?

Answer 1

After some research, Propel's ModelCriteria API doesn't support arbitrary joins, as explained in the Propel Users group here. ModelCriteria only works where the schema defines relationships, and as the table in the above example doesn't reference itself explicitly, it can't be done without using old Criterias.

Propel 1.6 does, however, support multiple conditions on a join, as described in the documentation, if that's useful for anyone. You would have to make sure you have Propel 1.6, however.

Instead, I had to revert back to a sub-query, which Propel 1.6 also now supports with addSelectQuery. I modified the SQL to look like this:

SELECT * FROM (
  SELECT * FROM machine_location
  ORDER BY time DESC
) AS times GROUP BY times.machine_id;

This could then be written using Propel:

$times = MachineLocationQuery::create()
  ->orderByTime('desc');
$latestLocations = MachineLocationQuery::create()
  ->addSelectQuery($times, 'times')
  ->groupBy('MachineId')
  ->find();

Be careful when reading the documentation; it's addSelectQuery(), not useSelectQuery().

To accommodate and use within a Query call and allow for joins, I had to convert the items to an array containing their primary keys, and return a search for those primary keys. Propel seemed to choke if I returned the above Query object, sans find.

return $this
  ->filterByPrimaryKeys($latestLocations->toKeyValue('MachineId', 'Id'));