如何为现有Company实体添加额外SELECT表达式查询结果？

Got it, let's break down how to add custom SELECT expressions (like your multi-dimensional company score) to your existing Company repository query that returns a Paginator instance. I'll walk you through the steps with concrete examples.

1. Start with Your Existing Query #

First, let's assume your base query looks something like this (filling in context for clarity):

public function findCompaniesWithPagination(int $page, int $limit): Paginator
{
    $qb = $this->createQueryBuilder('c')
        ->orderBy('c.id', 'ASC');

    $query = $qb->getQuery()
        ->setFirstResult(($page - 1) * $limit)
        ->setMaxResults($limit);

    return new Paginator($query);
}

2. Add Your Custom SELECT Expression #

Use addSelect() (or replace select() for full control) to include your score calculation. Let's say your score combines revenue and employee count as an example:

$qb = $this->createQueryBuilder('c')
    ->select('c') // Keep selecting the full Company entity
    ->addSelect('(c.revenue * 0.3 + c.employee_count * 0.7) AS company_score') // Your custom scoring expression with alias
    ->orderBy('company_score', 'DESC') // Optional: Sort results by your new score
    ->addOrderBy('c.id', 'ASC');

3. Map the Extra Field to Your Entity (Optional but Recommended) #

To access the score directly from Company instances, add a non-persistent property to your Company class:

// src/Entity/Company.php
private ?float $companyScore = null;

public function getCompanyScore(): ?float
{
    return $this->companyScore;
}

public function setCompanyScore(?float $companyScore): void
{
    $this->companyScore = $companyScore;
}

Doctrine automatically maps the company_score query alias to the $companyScore property (it handles snake_case to camelCase conversion out of the box).

4. Fix Pagination Edge Cases #

The Paginator usually handles count queries automatically, but complex expressions might need tweaks:

• Disable Output Walkers: If you run into count query errors, try disabling output walkers (this fixes many pagination bugs with joins/grouping):

  $paginator = new Paginator($query);
  $paginator->setUseOutputWalkers(false);
  

• Manual Count Query: If the automatic count is incorrect, define a custom count query matching your main query's filters:

  $countQb = $this->createQueryBuilder('c')
      ->select('COUNT(DISTINCT c.id)')
      ->where($qb->getDQLPart('where')); // Reuse main query's WHERE conditions
  
  $paginator = new Paginator($query);
  $paginator->setCountQuery($countQb->getQuery());
  

5. Alternative: Return Array Results (No Entity Changes) #

If you don't want to modify the Company entity, switch to array hydration. This returns arrays containing all entity fields plus your custom score:

$query = $qb->getQuery()
    ->setFirstResult(($page - 1) * $limit)
    ->setMaxResults($limit)
    ->setHydrationMode(\Doctrine\ORM\Query::HYDRATE_ARRAY);

return new Paginator($query);

Each paginator item will now be an array like ['id' => 1, 'name' => 'Acme Corp', 'company_score' => 85.5].

内容的提问来源于stack exchange，提问作者Michał Szczech