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.