Take the 2-minute tour ×

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:

  id: ~

    type: integer
    foreignTable: machine
    type: integer
    foreignTable: location
    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)

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
    array(MachineLocationPeer::ID, MachineLocationPeer::TIME),

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?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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 machine_location
) AS times GROUP BY times.machine_id;

This could then be written using Propel:

$times = MachineLocationQuery::create()
$latestLocations = MachineLocationQuery::create()
  ->addSelectQuery($times, 'times')

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'));
share|improve this answer
add comment

Your Answer


