DAOs — querying and persisting

OrangeHRM has a single data-access pattern: plugin-specific <Name>Dao classes that extend OrangeHRM\Core\Dao\BaseDao. ~99 DAOs exist across all plugins, all following the same shape. They're plain classes (not Doctrine EntityRepository subclasses), they use a trait to talk to the EntityManager, and they build queries via Doctrine's QueryBuilder.

The path from API request to data is:

API endpoint handler
  ↓ calls a Service method (sometimes; often skipped for simple cases)
  ↓ calls a DAO method
  └── DAO: createQueryBuilder → joins → expressions → execute → return entities
       ↓ or: getRepository(Entity)->findOneBy([...]) for simple equality lookups

This skill covers the DAO layer. For the entity definitions themselves see entities; for how the FilterParams DTO flows in from the API layer see rest-endpoints.

The base class — BaseDao

namespace OrangeHRM\X\Dao;

use OrangeHRM\Core\Dao\BaseDao;
use OrangeHRM\Entity\Widget;
use OrangeHRM\X\Dto\WidgetSearchFilterParams;

class WidgetDao extends BaseDao
{
    public function getWidgetById(int $id): ?Widget
    {
        return $this->getRepository(Widget::class)->find($id);
    }

    public function getWidgetList(WidgetSearchFilterParams $params): array
    {
        return $this->getWidgetListPaginator($params)->getQuery()->execute();
    }

    public function getWidgetCount(WidgetSearchFilterParams $params): int
    {
        return $this->getWidgetListPaginator($params)->count();
    }

    private function getWidgetListPaginator(WidgetSearchFilterParams $params): Paginator
    {
        $q = $this->createQueryBuilder(Widget::class, 'w');
        $this->setSortingAndPaginationParams($q, $params);
        // … filters from $params …
        return $this->getPaginator($q);
    }

    public function saveWidget(Widget $widget): Widget
    {
        $this->persist($widget);
        return $widget;
    }

    public function deleteWidgets(array $ids): int
    {
        $q = $this->createQueryBuilder(Widget::class, 'w')->delete();
        $q->where($q->expr()->in('w.id', ':ids'))->setParameter('ids', $ids);
        return $q->getQuery()->execute();
    }
}

That's the whole pattern. BaseDao itself is small:

abstract class BaseDao
{
    use EntityManagerHelperTrait;

    protected function count(QueryBuilder $qb): int;
    protected function setSortingParams(QueryBuilder $qb, FilterParams $filterParams): QueryBuilder;
    protected function setPaginationParams(QueryBuilder $qb, FilterParams $filterParams): QueryBuilder;
    protected function setSortingAndPaginationParams(QueryBuilder $qb, FilterParams $filterParams): QueryBuilder;
}

It contributes four FilterParams-aware helpers. The heavy lifting comes from EntityManagerHelperTrait.

EntityManagerHelperTrait — the full method catalog

Every DAO has access to these via BaseDao:

Method	Returns	Purpose
getEntityManager()	EntityManagerInterface	The DI-registered shared EM. Rarely called directly.
getRepository(string $entityClass)	EntityRepository<T>	For find, findBy, findOneBy, findAll.
persist($entity)	void	Persist + flush in one call. The codebase doesn't batch flushes.
remove($entity)	void	Remove + flush in one call.
createQueryBuilder(string $entityClass, string $alias, ?string $indexBy = null)	QueryBuilder	Start a new QB selecting from $entityClass AS $alias. Not Doctrine's repo-level createQueryBuilder — this one is on the trait.
getPaginator(QueryBuilder $qb)	Paginator	Wraps a QB for count() and paginated iteration. Use for counts on joined queries.
getQueryBuilderWrapper(QueryBuilder $qb)	QueryBuilderWrapper	Returns a thin wrapper exposing getQueryBuilder(). Use when a DAO method needs to return a "buildable" QB to its caller without executing it.
fetchOne(QueryBuilder $qb, int $offset = 0)	?object	Convenience for setFirstResult($offset)->setMaxResults(1)->execute()[0] ?? null.
getReference(string $entityName, $id)	?T	Returns a Doctrine proxy for the entity — no SELECT until accessed. Use for FK-binding (decorators' setXById pattern).
beginTransaction()	bool	Opens a DB transaction.
commitTransaction()	bool	Commits.
rollBackTransaction()	bool	Rolls back.

The trait wraps a DBAL Connection for transactions (not EntityManager::transactional, which exists but isn't used here).

The persist + flush convention

persist() in this codebase does both persist and flush, in one call:

protected function persist($entity): void
{
    $this->getEntityManager()->persist($entity);
    $this->getEntityManager()->flush();
}

This means the codebase does not batch writes through Doctrine's unit-of-work. Every save = one flush. Same for remove(). If you find yourself wanting to batch:

• For inserts/updates of many entities of the same type: use a DAO method that does $qb->insert() / $qb->update() directly (one SQL statement).
• For a multi-step operation that must be atomic: wrap in a transaction (see below). Even with one-flush-per-persist, the transaction guarantees rollback on failure.

Don't try to use $em->persist() directly to skip the flush — the convention is enforced for predictability.

Simple queries — getRepository() + find, findOneBy, findBy

For pure equality lookups (SELECT * FROM x WHERE foo = ? AND bar = ?), skip the QueryBuilder:

// By primary key
$widget = $this->getRepository(Widget::class)->find($id);

// By criteria — single result
$dependent = $this->getRepository(EmpDependent::class)->findOneBy([
    'employee' => $empNumber,
    'name'     => $name,
]);

// By criteria — multiple results
$attachments = $this->getRepository(EmployeeAttachment::class)->findBy([
    'employee' => $empNumber,
]);

~10 usages across PimPlugin DAOs (EmployeeDependentDao, EmpEmergencyContactDao, EmployeeImmigrationRecordDao, EmployeeMembershipDao, etc.). The pattern is: when the criteria are a flat set of field = value AND conditions, findOneBy is shorter than building a QB. Anything more complex (LIKE, OR, joins, IN, NULL checks, sort/page) goes through QueryBuilder.

findBy accepts criteria, an optional orderBy array, limit, offset — but at that point you should be using a QB anyway, for visibility.

QueryBuilder — the everyday case

createQueryBuilder(Entity::class, 'alias') returns a Doctrine QueryBuilder with SELECT $alias FROM $entityClass AS $alias pre-filled. Build from there.

Joins by relation name

Use the relation property name from the entity, not raw table names:

$q = $this->createQueryBuilder(Employee::class, 'employee');
$q->leftJoin('employee.jobTitle',  'jobTitle');     // ManyToOne
$q->leftJoin('employee.subDivision', 'subunit');    // ManyToOne
$q->leftJoin('employee.locations',   'location');   // ManyToMany
$q->leftJoin('employee.supervisors', 'supervisor'); // self-referential ManyToMany

innerJoin / leftJoin semantics same as SQL. addSelect('jobTitle') to also hydrate the joined entities (avoids N+1 if you're going to read job title fields).

Expression builder

$q->expr() returns Doctrine's Expr class. Heavy usage across DAOs:

$q->andWhere($q->expr()->isNull('employee.employeeTerminationRecord'));
$q->andWhere($q->expr()->isNotNull('employee.employeeTerminationRecord'));
$q->andWhere($q->expr()->eq('jobTitle.id', ':id'));
$q->andWhere($q->expr()->in('employee.empNumber', ':empNumbers'));
$q->andWhere($q->expr()->notIn('employee.empNumber', ':excluded'));
$q->andWhere($q->expr()->like('employee.firstName', ':name'));

// OR with multiple conditions
$q->andWhere($q->expr()->orX(
    $q->expr()->like('employee.firstName', ':name'),
    $q->expr()->like('employee.lastName',  ':name'),
    $q->expr()->like(
        $q->expr()->concat(
            'employee.firstName',
            $q->expr()->literal(' '),
            'employee.lastName',
        ),
        ':name'
    ),
));

The pattern in EmployeeDao (search by name across firstName, lastName, middleName, and concatenated full name) is worth copying when you need a similar "search anywhere" feature.

literal() produces a quoted string literal in DQL — safe for SQL because Doctrine handles quoting. Don't try to interpolate user input into expressions; always use named parameters (:name) and setParameter('name', $value).

Parameter binding

$q->setParameter('name', '%' . $userInput . '%');                 // for LIKE
$q->setParameter('empNumbers', [1, 2, 3]);                        // for IN — array
$q->setParameter('startDate', $date, Types::DATE_MUTABLE);        // explicit type for date/time

Never concatenate user input into the DQL string. All filters go through setParameter. Doctrine binds them and the underlying DBAL escapes correctly.

Conditional joins

When a join is only needed for some queries (e.g. sorting by a supervisor's name), check the FilterParams and add the join lazily:

$joinedSupervisors = false;
if ($this->getTextHelper()->strStartsWith($params->getSortField(), 'supervisor')) {
    $q->leftJoin('employee.supervisors', 'supervisor');
    $joinedSupervisors = true;
}

// later, when applying a filter that also needs supervisor:
if (!is_null($params->getSupervisorEmpNumbers())) {
    if (!$joinedSupervisors) {
        $q->leftJoin('employee.supervisors', 'supervisor');
        $joinedSupervisors = true;
    }
    $q->andWhere($q->expr()->in('supervisor.empNumber', ':supervisorEmpNumbers'))
      ->setParameter('supervisorEmpNumbers', $params->getSupervisorEmpNumbers());
}

The $joinedSupervisors flag prevents double-joining. Pattern from EmployeeDao::getEmployeeListQueryBuilderWrapper.

Result retrieval

Choose by what you want:

$entities = $q->getQuery()->execute();              // = getResult() — array of hydrated entities
$entities = $q->getQuery()->getResult();            // same thing, explicit

$arrays = $q->getQuery()->getArrayResult();         // arrays instead of entities (faster, no proxies)

$entity = $q->getQuery()->getOneOrNullResult();     // single entity or null
$entity = $q->getQuery()->getSingleResult();        // throws if 0 or >1 rows

$value = $q->getQuery()->getSingleScalarResult();   // single scalar (e.g. COUNT)
$values = $q->getQuery()->getScalarResult();        // array of arrays of scalars

getArrayResult() is the right choice when you only need IDs or a few columns — avoids the cost of entity hydration. EmployeeDao uses it for getEmpNumbersByFilterParams:

$q->select('employee.empNumber');
$result = $q->getQuery()->getArrayResult();
return array_column($result, 'empNumber');

The array_column(..., 0) trick

When a query selects [entity, extraField] (e.g. to enable sorting by a computed expression that needs to be in SELECT), the result is an array of [Entity, $extraField] pairs. Extract the entities with:

$qb->addSelect('employee.empNumber');  // forces empNumber into select for sorting
$rows = $q->getQuery()->execute();
return array_column($rows, 0);          // returns just the Employee objects

Used in EmployeeDao when sorting requires addSelect of an extra column. The 0 is the index of the entity in each pair.

Blob columns — partial-DTO projection for list queries

Several entities have type="blob" columns holding raw file/image bytes — EmployeeAttachment, EmpPicture, JobSpecificationAttachment, ClaimAttachment, CandidateAttachment, InterviewAttachment, VacancyAttachment, BuzzPhoto, Theme (corporate-branding logo). Hydrating these entities into a list query is expensive — every row drags the binary content into memory and over the wire, often megabytes per row, even though the table view only needs the filename, size, MIME type, and uploaded-at metadata.

OrangeHRM's convention is a Partial<EntityName> DTO + the NEW PartialX::class(...) DQL projection. The query never reads the blob column at all.

The pattern, end-to-end

1. Define the Partial DTO in <Plugin>/Dto/Partial<EntityName>.php

Plain class with the non-blob columns as constructor params:

namespace OrangeHRM\Pim\Dto;

use DateTime;
use OrangeHRM\Core\Traits\Service\DateTimeHelperTrait;

class PartialEmployeeAttachment
{
    use DateTimeHelperTrait;

    public function __construct(
        private ?int $attachId,
        private ?string $description,
        private ?string $filename,
        private ?int $size,
        private ?string $fileType,
        private ?int $attachedBy,
        private ?string $attachedByName,
        private ?DateTime $dateTime,    // gets split into attachedDate + attachedTime in setters
    ) {
        $this->setAttachedDate($dateTime);
        $this->setAttachedTime($dateTime);
    }

    public function getAttachId(): ?int     { return $this->attachId; }
    public function getFilename(): ?string  { return $this->filename; }
    public function getSize(): ?int         { return $this->size; }
    public function getFileType(): ?string  { return $this->fileType; }
    // … etc — getters only, no setters except for derived fields
}

Constructor parameter names must match the SELECT order exactly — Doctrine's NEW syntax is positional, not name-based.

2. Project to the DTO in the DAO

namespace OrangeHRM\Pim\Dao;

use OrangeHRM\Core\Dao\BaseDao;
use OrangeHRM\Entity\EmployeeAttachment;
use OrangeHRM\Pim\Dto\PartialEmployeeAttachment;

class EmployeeAttachmentDao extends BaseDao
{
    public function getEmployeeAttachments(int $empNumber, string $screen): array
    {
        $select = 'NEW ' . PartialEmployeeAttachment::class
                . '(a.attachId, a.description, a.filename, a.size, a.fileType,
                    a.attachedBy, a.attachedByName, a.attachedTime)';

        $q = $this->createQueryBuilder(EmployeeAttachment::class, 'a');
        $q->select($select);
        $q->andWhere('a.employee = :empNumber')->setParameter('empNumber', $empNumber);
        $q->andWhere('a.screen = :screen')->setParameter('screen', $screen);
        $q->addOrderBy('a.attachId', ListSorter::ASCENDING);

        return $q->getQuery()->execute();
    }
}

select() with a NEW <FQCN>(...) string tells Doctrine to instantiate the DTO directly instead of hydrating the entity. The blob column never appears in the SELECT clause, so it never travels from the DB.

3. Selecting an FK ID without loading the related entity — IDENTITY()

When the partial needs a foreign-key column but you don't want to also hydrate the parent:

$select = 'NEW ' . PartialJobSpecificationAttachment::class
        . '(js.id, js.fileName, js.fileType, js.fileSize, IDENTITY(js.jobTitle))';

IDENTITY(js.jobTitle) returns the FK id as a scalar without joining/loading JobTitle. Pair with a ?int $jobTitleId parameter on the DTO constructor.

Two related DAO methods, one entity

The convention is to have both a partial-DTO method (for lists) and a full-entity method (for the download / picture-serve endpoint that actually needs the blob). See EmployeeAttachmentDao:

public function getEmployeeAttachments(int $empNumber, string $screen): array
{
    // returns PartialEmployeeAttachment[] — for the list / table UI
}

public function getEmployeeAttachment(int $empNumber, int $attachId, ?string $screen = null): ?EmployeeAttachment
{
    // returns the FULL entity including the blob — for the download endpoint
}

public function getPartialEmployeeAttachment(int $empNumber, int $attachId, ?string $screen): ?PartialEmployeeAttachment
{
    // returns the partial DTO for a single attachment — for edit-form metadata that doesn't need the file
}

The single-record full fetch goes through findOneBy() and gets the entity (and the blob). The list goes through the partial projection. The DAO API exposes both shapes because the consumer knows which it needs.

EmpPicture — the orthogonal "fetch blob via dedicated endpoint with ETag caching" pattern

For EmpPicture (employee profile photo), the blob isn't fetched in any list endpoint at all. The list endpoints return just the metadata, and the picture itself is served by a dedicated REST endpoint (/api/v2/pim/employees/{empNumber}/picture) with ETag-based HTTP caching (see rest-endpoints for ETagHelperTrait and the file-controller pattern). Browsers cache the picture by ETag and only re-fetch when it changes. The Vue side embeds <img src="…/picture"> and lets HTTP do the caching.

This is the better pattern when the blob is shown in the UI on every row (avatar, thumbnail) — projecting to a partial DTO solves the list-query cost, but the browser still needs the binary somehow. The dedicated-endpoint + ETag approach lets list responses stay lean and lets the browser cache binaries individually.

For attachments that are downloaded on demand (employee documents, claim receipts), the partial-DTO list + on-demand full-entity fetch is enough — the binary is only paid for when the user clicks download.

Pagination — use Paginator for counts

Counts on joined queries can't be done by replacing SELECT with COUNT(*) — duplicates from JOIN/DISTINCT throw the count off. Doctrine's Paginator handles this correctly:

private function getJobTitlesPaginator(JobTitleSearchFilterParams $params): Paginator
{
    $q = $this->createQueryBuilder(JobTitle::class, 'jt');
    $this->setSortingAndPaginationParams($q, $params);
    // … filters …
    return $this->getPaginator($q);
}

public function getJobTitles(JobTitleSearchFilterParams $params): array
{
    return $this->getJobTitlesPaginator($params)->getQuery()->execute();
}

public function getJobTitlesCount(JobTitleSearchFilterParams $params): int
{
    return $this->getJobTitlesPaginator($params)->count();
}

Always pair list + count via the same Paginator-builder method. This guarantees the filter set used for the list matches the count — drift between them is the most common cause of "total says 137 but I see 200 rows" bugs.

setSortingAndPaginationParams($q, $filterParams) (from BaseDao):

protected function setSortingAndPaginationParams(QueryBuilder $qb, FilterParams $filterParams): QueryBuilder
{
    if (!is_null($filterParams->getSortField())) {
        $qb->addOrderBy($filterParams->getSortField(), $filterParams->getSortOrder());
    }
    if (!empty($filterParams->getLimit())) {                 // limit=0 means "no limit"
        $qb->setFirstResult($filterParams->getOffset())
           ->setMaxResults($filterParams->getLimit());
    }
    return $qb;
}

limit=0 disables pagination — the FilterParams convention. Use it for reports or exports that need the full set.

QueryBuilderWrapper — returning a buildable QB

When a DAO method builds a QB but the caller wants to layer more on top (additional filters, different ordering), return a QueryBuilderWrapper instead of executing:

protected function getEmployeeListQueryBuilderWrapper(EmployeeSearchFilterParams $params): QueryBuilderWrapper
{
    $q = $this->createQueryBuilder(Employee::class, 'employee');
    // … standard joins and filters …
    return $this->getQueryBuilderWrapper($q);
}

public function getEmployeeList(EmployeeSearchFilterParams $params): array
{
    $qb = $this->getEmployeeListQueryBuilderWrapper($params)->getQueryBuilder();
    return array_column($qb->getQuery()->execute(), 0);
}

public function getEmpNumbersByFilterParams(EmployeeSearchFilterParams $params): array
{
    $params->setSortField('employee.empNumber');
    $q = $this->getEmployeeListQueryBuilderWrapper($params)->getQueryBuilder();
    $q->select('employee.empNumber');                                // override what's selected
    return array_column($q->getQuery()->getArrayResult(), 'empNumber');
}

QueryBuilderWrapper is intentionally thin — just a typed return so the caller knows it's getting a QB-in-progress, not an executed result. Used heavily in EmployeeDao.

The FilterParams flow

FilterParams DTOs are introduced in the rest-endpoints skill (the API layer creates them and binds query params). The DAO consumes them via BaseDao::setSortingAndPaginationParams and individual filter getters:

public function getEmployeeListQueryBuilderWrapper(EmployeeSearchFilterParams $params): QueryBuilderWrapper
{
    $q = $this->createQueryBuilder(Employee::class, 'employee');

    $this->setSortingAndPaginationParams($q, $params);

    if (!is_null($params->getName())) {                              // each filter is "if not null, apply"
        $q->andWhere($q->expr()->like('employee.firstName', ':name'))
          ->setParameter('name', '%' . $params->getName() . '%');
    }

    if (!is_null($params->getJobTitleId())) {
        $q->andWhere('jobTitle.id = :jobTitleId')
          ->setParameter('jobTitleId', $params->getJobTitleId());
    }

    // …

    return $this->getQueryBuilderWrapper($q);
}

The convention: one if-block per filter, all andWhere. Don't try to combine filter logic across blocks; keeping each one independent makes the code grep-able and the SQL inspectable.

SubunitIdChainTrait — subunit-tree filtering

When a filter "include this subunit" should mean "include this subunit AND all its children", the OrangeHRM\Pim\Dto\Traits\SubunitIdChainTrait provides:

// On the FilterParams DTO
class EmployeeSearchFilterParams extends FilterParams
{
    use SubunitIdChainTrait;
    protected ?int $subunitId = null;
    // …
}

// In the DAO:
if (!is_null($params->getSubunitId())) {
    $q->andWhere($q->expr()->in('subunit.id', ':subunitIds'))
      ->setParameter('subunitIds', $params->getSubunitIdChain());    // ← the chain, not just the ID
}

The trait memoizes the chain per DTO instance (calls CompanyStructureService::getSubunitChainById($subunitId) once). Mirror this pattern for any other tree-shaped filter that needs subtree expansion.

Transactions

EntityManagerHelperTrait exposes the DBAL connection's transaction methods:

public function importEmployees($csvContent): array
{
    $this->beginTransaction();
    try {
        $result = $this->getPimCsvDataImportService()->import($csvContent);
        $this->commitTransaction();
        return $result;
    } catch (CSVUploadFailedException $e) {
        $this->rollBackTransaction();
        throw new BadRequestException($e->getMessage());
    } catch (Exception $e) {
        $this->rollBackTransaction();
        throw new TransactionException($e);
    }
}

When to use:

• Multi-table writes that must be atomic — CSV import, bulk role-permission seeding, defined-report delete (which removes rows from multiple tables).
• Anywhere a partial failure would leave inconsistent state — e.g. creating an employee + its initial salary + its work shift assignments.

Where to place the boundary:

• Inside a DAO method if the transaction is scoped to a single DAO's operations (e.g. CustomFieldDao::deleteFieldAndItsValues).
• Inside an API endpoint handler if the transaction spans multiple DAOs/services (e.g. EmployeeCSVImportAPI::create, PimDefinedReportAPI::delete).

Do not use EntityManager::transactional() (the closure-based API). It exists in Doctrine but isn't used in this codebase — stick to explicit begin/commit/rollback for consistency.

What's NOT in the codebase