原则2 - 从历史表中检索最新记录

Using a status_history table that will save the date and time a status was modified for a particular record in entries, I'm trying to find the easiest way to also get the current status, AKA, the status with the lastest date.

For instance consider the following: status_history

+----+----------+------------+-----------+
| id | entry_id |   status   | timestamp |
+----+----------+------------+-----------+
|  1 |        1 | Processing | 15:05:09  |
|  2 |        1 | In Review  | 15:05:18  |
|  3 |        1 | Complete   | 15:05:26  |
+----+----------+------------+-----------+

And of course, our entry: entries

+----+--------+
| id | title  |
+----+--------+
|  1 | Foobar |
+----+--------+

How can I retrieve, with DQL, the the latest status (technically the current status) for a particular entry, without having to maintain a latest_status_id column in my Entry entity?

Consider the call $entry->getCurrentStatus() would return "Complete". However, in the case we have a great amount of status records for that entry, a second query to load all statuses might turn into a performance drop.

NOTE: In this article they describe it with the extra column approach, using a locking mechanism for concurrency. I'd like to avoid this approach unless this is the correct one, since we're trying not to have double relations for each table (status_history.entry_id and entry.latest_history_id).

I think using a QueryBuilder inside your status history repository this would be sufficient:

$entry_id = 1;
$queryBuilder = $this->createQueryBuilder()
    ->select('s')
    ->where('s.entry = :entry_id')
    ->orderBy('o.id', 'DESC')
    ->setParameter('entry_id', $entry_id)
$query = $queryBuilder->getQuery();
return $query->getSingleResult();

You can use id assuming that id is "auto increment" and the timestamp is an automatic time-stamp of creation of your records. In such case highest id is always the latest insert, so you don't even need to use the timestamp column to find that record.

Otherwise you have to add an index to your timestamp column and order by that column instead:

$entry_id = 1;
$queryBuilder = $this->createQueryBuilder()
    ->select('s')
    ->where('s.entry = :entry_id')
    ->orderBy('o.timestamp', 'DESC')
    ->setParameter('entry_id', $entry_id)
$query = $queryBuilder->getQuery();
return $query->getSingleResult();

You need to add an index to your timestamp column for increased performance

For a single entry the query could look something like this:

$query = $em->createQuery('SELECT e, s 
    FROM entries e 
    JOIN e.status_history s 
    WHERE e.entry_id = :entry_id
    ORDER BY s.timestamp DESC');
$query->setParameter('entry_id', $entry_id);
$entries = $query->getSingleResult();

In your getCurrentStatus() method you can return the only status_history object that is assigned.

For all entries it is more complex and you have to use a subquery with GROUP BY and MAX(). It is feasible in native SQL but I haven't figured out how to do it with DQL. I will update my answer as soon as I've got a solution.