php - Finding last entity of a joined entity in entityRepository (Symfony2 & Doctrine2)


In my Symfony2 app that represents an Asset Management System there is an entity called Asset. This entity has a OneToMany relationship with another entity called AssetLog. Each AssetLog is linked to one Status which is also an entity.

For example: There might be an asset called Car. This car has a log of what has happened to it, when it has happened and who is responsible for this. Each log entry is linked to a status which could be for: perfect condition, damaged, lost, stolen, etc.

What I am trying to do is writing a query in the assetRepository that will give me all assets with a certain status, say Stolen.

This means for example: all cars of which their most recent log entry is linked to the status Stolen.

I am struggling with the fact that I need the last log entry of each asset. And only if that entry is linked to a specific status, do I want the asset.

In my specific example cars that were once stolen but returned again, should not appear in the list.

Does anyone know if this is possible using the Querybuilder, and if so, how to do it?


Maybe it will help if I describe what I've done to get the desired result in SQL. This is my SQL-query:

select *
from asset a
join asset_log l
on l.asset_id =
left join asset_log l2
on l2.asset_id = l.asset_id
and >
where l.receiver_id = 12345
group by l.asset_id

This works perfectly, but how do write this in DQL? This is my best attempt:

$qb->select('a', 'l', 'l2')
->from('AssetBundle:Asset', 'a')
->join('a.logEntries', 'l')
->leftJoin('a.logEntries', 'l2', 'ON', 'l2.asset = l.asset')
->where("l.receiver = 12345")
->andWhere(' >')

This DQL query is executed without error, but gives a different result: assets that should be visible (as they are in the SQL-query) are missing.




I had this problem once in "normal" SQL. You'll need a subquery for that: Create a WHERE condition that compares the current timestamp (or sequence number, if you like) of each log entry with the maximum timestamp of this log. To get this maximum you need the subquery.

Something like this (I'm not quite sure, if my subquery satisfies doctrine syntax; but I think you'll get the idea):

JOIN a.log l
JOIN l.status s
WHERE s.statusCode = ?
AND (l.timestamp = (SELECT MAX(timestamp) FROM log l2 WHERE l2.assetId =

People are also looking for solutions to the problem: character encoding - PHP how to display text from input tag correctly


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.