How to select records with ignoring by value of fields of real links?

Question

SQL

SELECT u0_.email     AS email_3,
       u0_.is_active AS is_active_5
    FROM user_email      u0_
        INNER JOIN users u1_ ON u0_.user_uuid = u1_.uuid
    WHERE u0_.email = '[email protected]'
      AND u0_.is_active = TRUE
      AND u1_.uuid NOT IN (
                                  SELECT u1_.uuid
                                      FROM users AS               u1_
                                          INNER JOIN social_token s2_ ON u1_.uuid = s2_.user_uuid
                                          INNER JOIN auth_token   a3_ ON u1_.uuid = a3_.user_uuid
                                      WHERE a3_.secret =
                                            'token'
                                         OR s2_.secret =
                                            'token'
                              )

The request is working, but I doubt the ego performance and the correctness of NOT IN clause AND u1_.nickname NOT IN (.

The task is to choose to exclude those emails from the request where the user has such a token.
When sending a request to update an entity, it is necessary to ignore the unique fields of the current user, for example, if an email is transmitted and it is the same as the current one, it should not be validated for uniqueness.
In the request, we have the current authorized user token and email.

Answer 1

class DQLUserEmailQueryFunctions extends AbstractDQLQueryFunctions {
    protected string $repositoryClass   = UserEmail::class;
    protected string $repositoryAlias   = 'userEmailTableAlias';
    protected string $userAlias         = 'user';
    protected string $authTokensAlias   = 'authTokens';
    protected string $socialTokensAlias = 'socialTokens';

    /**
     * Creates a new QueryBuilder instance that is pre populated for this entity name.
     */
    protected function createQueryBuilder(): QueryBuilder {
        return $this->entityManager->createQueryBuilder()->select($this->repositoryAlias)->from(
            $this->repositoryClass,
            $this->repositoryAlias
        )
            ;
    }

....


 $subQuery = $this->entityManager->createQueryBuilder()
                                            ->select($this->userAlias)
                                            ->from(User::class, $this->userAlias)
            ;
            $this->innerJoinTablesIfNotJoined(
                $subQuery,
                $this->userAlias,
                [$this->socialTokensAlias, $this->authTokensAlias]
            );
            $subQuery->where(
                $expr->orX(
                    $expr->eq($tableFields['socialTokenSecret'], sprintf(':%s', 'socialTokenSecret')),
                    $expr->eq($tableFields['authTokenSecret'], sprintf(':%s', 'authTokenSecret'))
                )
            );
            $query->andWhere($expr->not($expr->exists($subQuery->getDQL())))
                  ->setParameter('socialTokenSecret', $filter->ignoreByTokenSecret, Types::TEXT)
                  ->setParameter('authTokenSecret', $filter->ignoreByTokenSecret, Types::TEXT)
            ;

DQL

SELECT u0_.created_at AS created_at_0, u0_.updated_at AS updated_at_1, u0_.uuid AS uuid_2, u0_.email AS 
email_3, u0_.is_fallback AS is_fallback_4, u0_.is_active AS is_active_5, u0_.user_uuid AS user_uuid_6 
FROM user_email u0_ 
WHERE u0_.email = ? 
AND u0_.is_active = ? 
AND (NOT (EXISTS (
SELECT u1_.uuid 
FROM users u1_
 INNER JOIN social_token s2_ ON u1_.uuid = s2_.user_uuid 
INNER JOIN auth_token a3_ ON u1_.uuid = a3_.user_uuid 
WHERE s2_.secret = ? OR a3_.secret = ?
)))

SQL

SELECT u0_.email     AS email_3,
       u0_.is_active AS is_active_5
    FROM user_email             u0_
    WHERE u0_.email = '[email protected]'
      AND u0_.is_active = TRUE
      AND NOT EXISTS(
            SELECT u1_.uuid
                FROM users AS u1_
                    INNER JOIN social_token s2_ ON u1_.uuid = s2_.user_uuid
                    INNER JOIN auth_token   a3_ ON u1_.uuid = a3_.user_uuid
                WHERE a3_.secret =
                      'auth_token'
                   OR s2_.secret =
                      'auth_token'
        )
;

Performance did not check, but it's clear that native SQL can be done many times faster.
Because this query solves the problem faster NOT IN I think so far and this is enough.
I would be grateful for answers in native esquel