将结果作为Doctrine实体中的字段进行查询

As part of a login system I have a User entity which I need to add 'Last Login' and 'Total Logins' information to.

I have a couple of tables to represent this, the user table and the user_log table. The user_log table captures other log actions, so I need to select only a specific type (LOGIN).

Basically I need to know how to express the following query in terms of doctrine entities:

SELECT username, 
MAX(user_log.log_time) AS last_login, 
(SELECT COUNT(id) FROM user_log 
    WHERE user_log.user_id = user.id 
    AND user_log.log_action = "LOGIN") AS login_count
FROM user 
LEFT JOIN user_log ON user_log.log_action = "LOGIN" 
AND user_log.user_id = user.id

I've tried using DQL, the problem is the DQL ran the correct query but it didn't work because I don't know how to create this kind of relationship in doctrine entities.

The only solution I can think of at the moment is to use the query above in a custom repository method and manually create / return the entities. If possible though I'd like to set up the relationships using annotations so that I can fetch this user information using the built in repository methods.

Any help would be greatly appreciated, thanks!