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!
