Symfony Profiler

<?php
namespace CodersLab\Lms\Modules\IdentityAccess\Infrastructure\Read;
use CodersLab\Lms\Modules\IdentityAccess\Application\Query\Model\User;
use CodersLab\Lms\Modules\IdentityAccess\Application\Query\UserQuery;
use CodersLab\Lms\SharedKernel\Application\TenantLanguages;
use CodersLab\Lms\SharedKernel\Common\Exception\NotFoundException;
use CodersLab\Lms\SharedKernel\Domain\Identity\Uuid;
use CodersLab\Lms\SharedKernel\Domain\Role;
use DateTime;
use Doctrine\DBAL\Connection;
final class DbalUserQuery implements UserQuery
{
private Connection $connection;
private TenantLanguages $tenantLanguages;
public function __construct(Connection $connection, TenantLanguages $tenantLanguages)
{
$this->connection = $connection;
$this->tenantLanguages = $tenantLanguages;
}
public function getById(int $id): User
{
return $this->getBy('id', (string)$id);
}
public function getByEmail(string $email): User
{
return $this->getBy('email', $email);
}
public function getRoles(int $id): array
{
$roles = $this->connection->fetchOne(
' SELECT roles FROM fos_user WHERE id = :uid',
[
'uid' => $id,
]
);
$roles = unserialize($roles) ?? [];
return array_map(fn (string $role) => new Role($role), $roles);
}
public function hasCourseWithRepositories(int $id): bool
{
$sql = <<<SQL
SELECT COUNT(i.id) FROM ia_user_access ua
JOIN instance i on ua.instance_id = i.id
JOIN materials_course mc on i.course_id = mc.id
WHERE mc.repositories = 1 AND ua.user_id = :id
SQL;
$stmt = $this->connection->executeQuery($sql, [
'id' => $id,
]);
return (int)$stmt->fetchOne() > 0;
}
public function getByGithubLogin(string $githubLogin): User
{
return $this->getBy('github', $githubLogin);
}
public function lastSeenMaterial(string $id): ?User\LastSeenMaterial
{
$sql = <<<SQL
SELECT iaua.instance_id,
iaua.last_seen_material_id material_id,
iaua.last_seen_material_date date,
mct.chapter_id
FROM ia_user_access iaua
JOIN fos_user fu on iaua.user_id = fu.id
JOIN instance i on i.id = iaua.instance_id
JOIN materials_course_tree mct on mct.course_id = i.course_id and mct.material_id = iaua.last_seen_material_id
WHERE iaua.user_id = :id
AND iaua.last_seen_material_id IS NOT NULL
AND fu.show_welcome_page = 1
ORDER BY iaua.last_seen_material_date DESC
LIMIT 1
SQL;
$stmt = $this->connection->executeQuery($sql, [
'id' => $id,
]);
$res = $stmt->fetchAssociative();
if ($res !== false) {
return new User\LastSeenMaterial(
new Uuid($res['material_id']),
$res['instance_id'],
new DateTime($res['date']),
new Uuid($res['chapter_id'])
);
}
return null;
}
/**
* @return array<User>
*/
public function findUsersWithoutActivation(\DateTimeImmutable $registerDate): array
{
$qb = $this->connection->createQueryBuilder();
$qb->select([
'u.id',
'u.name',
'u.surname',
'u.email',
'u.roles',
'u.github',
'u.github_id',
'u.language',
'u.created',
])->from('fos_user', 'u')
->where('u.enabled = 0')
->andWhere('CAST(u.created as DATE) = :date')
->setParameter('date', $registerDate->format('Y-m-d'));
$data = $qb->execute()->fetchAllAssociative();
return array_map(function (array $row) {
$roles = unserialize($row['roles']);
$roles = array_filter($roles);
$roles = empty($roles) ? [Role::USER] : $roles;
return new User(
(int)$row['id'],
$row['name'],
$row['surname'],
$row['email'],
$row['github'],
$row['github_id'],
'https://www.gravatar.com/avatar/' . md5($row['email']),
$roles,
new User\Redirect(User\RedirectType::LIST(), []),
$row['lang'] ?? $this->tenantLanguages->getDefaultLocale(),
$this->tenantLanguages->getUserLanguages(),
false,
new \DateTimeImmutable($row['created'])
);
}, $data);
}
private function getBy(string $parameter, string $value): User
{
$qb = $this->connection->createQueryBuilder();
$qb->select([
'u.id',
'u.name',
'u.surname',
'u.email',
'u.roles',
'u.github',
'u.github_id',
'u.language',
'u.created',
])->from('fos_user', 'u')
->where('u.' . $parameter . ' = :parameter')
->setParameter('parameter', $value);
$data = $qb->execute()->fetchAssociative();
if ($data === false) {
throw NotFoundException::entityForCriteria('User', [$parameter => $value]);
}
$roles = unserialize($data['roles']);
$roles = array_filter($roles);
$roles = empty($roles) ? [Role::USER] : $roles;
$lastSeen = $this->lastSeenMaterial($data['id']);
if ($lastSeen instanceof User\LastSeenMaterial) {
$redirect = new User\Redirect(User\RedirectType::LAST_MATERIAL(), [
'materialId' => (string)$lastSeen->getId(),
'instanceId' => $lastSeen->getInstanceId(),
'chapterId' => (string)$lastSeen->getChapterId(),
]);
} else {
$redirect = new User\Redirect(User\RedirectType::LIST(), []);
}
return new User(
(int)$data['id'],
$data['name'],
$data['surname'],
$data['email'],
$data['github'],
$data['github_id'],
'https://www.gravatar.com/avatar/' . md5($data['email']),
$roles,
$redirect,
$data['lang'] ?? $this->tenantLanguages->getDefaultLocale(),
$this->tenantLanguages->getUserLanguages(),
false,
new \DateTimeImmutable($data['created'])
);
}
}

src/Lms/Modules/IdentityAccess/Infrastructure/Read/DbalUserQuery.php line 151