Select by user by last related entity?

Question

Doctrine ORM

User --> --> Email
User has many emails.
The active email is the one that was added last, the createdAt field.

1 - select user with active email
2 - select all users with active emails

Answer 1

In general, a bad idea, you need to do subqueries.
A user can have many emails.
To check if the email is busy, you need to make a subquery, sort by email.createAt DESC LIMIT 1 for each user, which creates a huge load.
I decided to add the $isActive field, the last email that was exposed will be $isActive = true.
Then you can avoid additional subqueries and make a selection
join user_emails .... where user_emails.isActive = true.
A user can have 2 active emails, one main and a second backup, so when checking for email availability, backup emails are also taken.
Old emails are needed for logging. in case of loss of access, facilitating account recovery.
Although it still added some overhead, it is necessary to update the previous email.
If the user has already used the email, then use it by replacing $$isActive