查询构建器 #

一、QueryBuilder基础 #

1.1 创建QueryBuilder #

php

<?php

namespace App\Repository;

use App\Entity\User;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

class UserRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, User::class);
    }

    public function findAllActive(): array
    {
        return $this->createQueryBuilder('u')
            ->where('u.active = :active')
            ->setParameter('active', true)
            ->orderBy('u.createdAt', 'DESC')
            ->getQuery()
            ->getResult();
    }
}

1.2 基本方法 #

php

<?php

class UserRepository extends ServiceEntityRepository
{
    public function findByName(string $name): array
    {
        return $this->createQueryBuilder('u')
            ->select('u.id', 'u.name', 'u.email')
            ->where('u.name LIKE :name')
            ->setParameter('name', '%' . $name . '%')
            ->getQuery()
            ->getResult();
    }

    public function findActiveUsers(int $limit = 10): array
    {
        return $this->createQueryBuilder('u')
            ->where('u.active = :active')
            ->setParameter('active', true)
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
    }

    public function countActiveUsers(): int
    {
        return $this->createQueryBuilder('u')
            ->select('COUNT(u.id)')
            ->where('u.active = :active')
            ->setParameter('active', true)
            ->getQuery()
            ->getSingleScalarResult();
    }
}

二、SELECT查询 #

2.1 选择字段 #

php

<?php

class UserRepository extends ServiceEntityRepository
{
    public function selectAll(): array
    {
        return $this->createQueryBuilder('u')
            ->getQuery()
            ->getResult();
    }

    public function selectSpecificFields(): array
    {
        return $this->createQueryBuilder('u')
            ->select('u.id', 'u.name', 'u.email')
            ->getQuery()
            ->getResult();
    }

    public function selectPartial(): array
    {
        return $this->createQueryBuilder('u')
            ->select('partial u.{id, name, email}')
            ->getQuery()
            ->getResult();
    }

    public function selectWithAlias(): array
    {
        return $this->createQueryBuilder('u')
            ->select('u.id as userId', 'u.name as userName')
            ->getQuery()
            ->getResult();
    }
}

2.2 聚合函数 #

php

<?php

class UserRepository extends ServiceEntityRepository
{
    public function countAll(): int
    {
        return $this->createQueryBuilder('u')
            ->select('COUNT(u.id)')
            ->getQuery()
            ->getSingleScalarResult();
    }

    public function getAverageAge(): float
    {
        return $this->createQueryBuilder('u')
            ->select('AVG(u.age)')
            ->getQuery()
            ->getSingleScalarResult();
    }

    public function getAgeStats(): array
    {
        return $this->createQueryBuilder('u')
            ->select('COUNT(u.id) as total')
            ->addSelect('AVG(u.age) as avgAge')
            ->addSelect('MIN(u.age) as minAge')
            ->addSelect('MAX(u.age) as maxAge')
            ->getQuery()
            ->getSingleResult();
    }

    public function groupByStatus(): array
    {
        return $this->createQueryBuilder('u')
            ->select('u.active')
            ->addSelect('COUNT(u.id) as count')
            ->groupBy('u.active')
            ->getQuery()
            ->getResult();
    }
}

三、WHERE条件 #

3.1 基本条件 #

php

<?php

class UserRepository extends ServiceEntityRepository
{
    public function findByStatus(bool $active): array
    {
        return $this->createQueryBuilder('u')
            ->where('u.active = :active')
            ->setParameter('active', $active)
            ->getQuery()
            ->getResult();
    }

    public function findByAgeRange(int $min, int $max): array
    {
        return $this->createQueryBuilder('u')
            ->where('u.age BETWEEN :min AND :max')
            ->setParameter('min', $min)
            ->setParameter('max', $max)
            ->getQuery()
            ->getResult();
    }

    public function findByNames(array $names): array
    {
        return $this->createQueryBuilder('u')
            ->where('u.name IN (:names)')
            ->setParameter('names', $names)
            ->getQuery()
            ->getResult();
    }

    public function findWithNullEmail(): array
    {
        return $this->createQueryBuilder('u')
            ->where('u.email IS NULL')
            ->getQuery()
            ->getResult();
    }
}

3.2 组合条件 #

php

<?php

class UserRepository extends ServiceEntityRepository
{
    public function findActiveAdults(): array
    {
        return $this->createQueryBuilder('u')
            ->where('u.active = :active')
            ->andWhere('u.age >= :age')
            ->setParameter('active', true)
            ->setParameter('age', 18)
            ->getQuery()
            ->getResult();
    }

    public function findByNameOrEmail(string $keyword): array
    {
        return $this->createQueryBuilder('u')
            ->where('u.name LIKE :keyword')
            ->orWhere('u.email LIKE :keyword')
            ->setParameter('keyword', '%' . $keyword . '%')
            ->getQuery()
            ->getResult();
    }

    public function findComplex(array $criteria): array
    {
        $qb = $this->createQueryBuilder('u');

        if (isset($criteria['active'])) {
            $qb->andWhere('u.active = :active')
               ->setParameter('active', $criteria['active']);
        }

        if (isset($criteria['minAge'])) {
            $qb->andWhere('u.age >= :minAge')
               ->setParameter('minAge', $criteria['minAge']);
        }

        if (isset($criteria['name'])) {
            $qb->andWhere('u.name LIKE :name')
               ->setParameter('name', '%' . $criteria['name'] . '%');
        }

        return $qb->getQuery()->getResult();
    }
}

3.3 表达式 #

php

<?php

use Doctrine\ORM\Query\Expr;

class UserRepository extends ServiceEntityRepository
{
    public function findWithExpr(): array
    {
        $qb = $this->createQueryBuilder('u');
        
        return $qb
            ->where($qb->expr()->eq('u.active', ':active'))
            ->andWhere($qb->expr()->gt('u.age', ':age'))
            ->andWhere($qb->expr()->like('u.name', ':name'))
            ->setParameter('active', true)
            ->setParameter('age', 18)
            ->setParameter('name', 'John%')
            ->getQuery()
            ->getResult();
    }

    public function findWithOrX(): array
    {
        $qb = $this->createQueryBuilder('u');
        
        return $qb
            ->where($qb->expr()->orX(
                $qb->expr()->eq('u.role', ':admin'),
                $qb->expr()->eq('u.role', ':superAdmin')
            ))
            ->setParameter('admin', 'admin')
            ->setParameter('superAdmin', 'super_admin')
            ->getQuery()
            ->getResult();
    }

    public function findWithAndX(): array
    {
        $qb = $this->createQueryBuilder('u');
        
        return $qb
            ->where($qb->expr()->andX(
                $qb->expr()->eq('u.active', ':active'),
                $qb->expr()->gte('u.age', ':minAge'),
                $qb->expr()->lte('u.age', ':maxAge')
            ))
            ->setParameter('active', true)
            ->setParameter('minAge', 18)
            ->setParameter('maxAge', 65)
            ->getQuery()
            ->getResult();
    }
}

四、JOIN关联 #

4.1 内连接 #

php

<?php

class ArticleRepository extends ServiceEntityRepository
{
    public function findWithAuthor(): array
    {
        return $this->createQueryBuilder('a')
            ->innerJoin('a.author', 'u')
            ->addSelect('u')
            ->getQuery()
            ->getResult();
    }

    public function findWithComments(): array
    {
        return $this->createQueryBuilder('a')
            ->innerJoin('a.comments', 'c')
            ->addSelect('c')
            ->where('c.status = :status')
            ->setParameter('status', 'approved')
            ->getQuery()
            ->getResult();
    }
}

4.2 左连接 #

php

<?php

class UserRepository extends ServiceEntityRepository
{
    public function findWithArticles(): array
    {
        return $this->createQueryBuilder('u')
            ->leftJoin('u.articles', 'a')
            ->addSelect('a')
            ->getQuery()
            ->getResult();
    }

    public function findUsersWithoutArticles(): array
    {
        return $this->createQueryBuilder('u')
            ->leftJoin('u.articles', 'a')
            ->where('a.id IS NULL')
            ->getQuery()
            ->getResult();
    }
}

4.3 关联条件 #

php

<?php

class ArticleRepository extends ServiceEntityRepository
{
    public function findWithPublishedComments(): array
    {
        return $this->createQueryBuilder('a')
            ->leftJoin('a.comments', 'c', 'WITH', 'c.status = :status')
            ->addSelect('c')
            ->setParameter('status', 'published')
            ->getQuery()
            ->getResult();
    }

    public function findWithRecentComments(): array
    {
        return $this->createQueryBuilder('a')
            ->leftJoin('a.comments', 'c', 'WITH', 'c.createdAt > :date')
            ->addSelect('c')
            ->setParameter('date', new \DateTime('-7 days'))
            ->getQuery()
            ->getResult();
    }
}

五、排序和分页 #

5.1 排序 #

php

<?php

class UserRepository extends ServiceEntityRepository
{
    public function findOrderedByName(): array
    {
        return $this->createQueryBuilder('u')
            ->orderBy('u.name', 'ASC')
            ->getQuery()
            ->getResult();
    }

    public function findOrderedByMultiple(): array
    {
        return $this->createQueryBuilder('u')
            ->orderBy('u.active', 'DESC')
            ->addOrderBy('u.createdAt', 'DESC')
            ->getQuery()
            ->getResult();
    }

    public function findWithCustomSort(string $field, string $direction = 'ASC'): array
    {
        return $this->createQueryBuilder('u')
            ->orderBy('u.' . $field, $direction)
            ->getQuery()
            ->getResult();
    }
}

5.2 分页 #

php

<?php

class UserRepository extends ServiceEntityRepository
{
    public function findPaginated(int $page, int $limit): array
    {
        return $this->createQueryBuilder('u')
            ->setFirstResult(($page - 1) * $limit)
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
    }

    public function findPaginatedWithCount(int $page, int $limit): array
    {
        $qb = $this->createQueryBuilder('u');
        
        $count = (clone $qb)
            ->select('COUNT(u.id)')
            ->getQuery()
            ->getSingleScalarResult();
        
        $results = $qb
            ->setFirstResult(($page - 1) * $limit)
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
        
        return [
            'items' => $results,
            'total' => $count,
            'page' => $page,
            'limit' => $limit,
            'pages' => ceil($count / $limit),
        ];
    }
}

六、DQL查询 #

6.1 基本DQL #

php

<?php

class UserRepository extends ServiceEntityRepository
{
    public function findActiveDql(): array
    {
        $dql = 'SELECT u FROM App\Entity\User u WHERE u.active = :active';
        
        return $this->getEntityManager()
            ->createQuery($dql)
            ->setParameter('active', true)
            ->getResult();
    }

    public function findWithJoinDql(): array
    {
        $dql = '
            SELECT a, u 
            FROM App\Entity\Article a 
            JOIN a.author u 
            WHERE a.status = :status
            ORDER BY a.createdAt DESC
        ';
        
        return $this->getEntityManager()
            ->createQuery($dql)
            ->setParameter('status', 'published')
            ->getResult();
    }
}

6.2 命名查询 #

php

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
#[ORM\NamedQueries([
    new ORM\NamedQuery(
        name: 'User.findActive',
        query: 'SELECT u FROM App\Entity\User u WHERE u.active = true ORDER BY u.name ASC'
    ),
    new ORM\NamedQuery(
        name: 'User.findByRole',
        query: 'SELECT u FROM App\Entity\User u WHERE u.role = :role'
    ),
])]
class User
{
}

php

<?php

class UserRepository extends ServiceEntityRepository
{
    public function findActiveNamed(): array
    {
        return $this->createNamedQuery('User.findActive')
            ->getResult();
    }

    public function findByRoleNamed(string $role): array
    {
        return $this->createNamedQuery('User.findByRole')
            ->setParameter('role', $role)
            ->getResult();
    }
}

七、子查询 #

7.1 子查询示例 #

php

<?php

class ArticleRepository extends ServiceEntityRepository
{
    public function findWithMostComments(): array
    {
        $subQb = $this->getEntityManager()->createQueryBuilder();
        
        $subQuery = $subQb
            ->select('COUNT(c.id)')
            ->from('App\Entity\Comment', 'c')
            ->where('c.article = a.id')
            ->getDQL();

        return $this->createQueryBuilder('a')
            ->select('a', '(' . $subQuery . ') as commentCount')
            ->orderBy('commentCount', 'DESC')
            ->setMaxResults(10)
            ->getQuery()
            ->getResult();
    }

    public function findAuthorsWithArticles(): array
    {
        $subQb = $this->getEntityManager()->createQueryBuilder();
        
        $subQuery = $subQb
            ->select('a.author')
            ->from('App\Entity\Article', 'a')
            ->where('a.status = :status')
            ->getDQL();

        return $this->createQueryBuilder('u')
            ->where($this->createQueryBuilder('u')->expr()->in('u.id', $subQuery))
            ->setParameter('status', 'published')
            ->getQuery()
            ->getResult();
    }
}

八、原生SQL #

8.1 原生查询 #

php

<?php

class UserRepository extends ServiceEntityRepository
{
    public function findByNativeSql(): array
    {
        $sql = 'SELECT * FROM users WHERE active = :active';
        
        $rsm = new \Doctrine\ORM\Query\ResultSetMappingBuilder($this->getEntityManager());
        $rsm->addRootEntityFromClassMetadata(User::class, 'u');
        
        return $this->getEntityManager()
            ->createNativeQuery($sql, $rsm)
            ->setParameter('active', 1)
            ->getResult();
    }

    public function findStats(): array
    {
        $sql = '
            SELECT 
                DATE(created_at) as date,
                COUNT(*) as count
            FROM users
            WHERE created_at > :date
            GROUP BY DATE(created_at)
            ORDER BY date DESC
        ';
        
        return $this->getEntityManager()
            ->getConnection()
            ->executeQuery($sql, ['date' => (new \DateTime('-30 days'))->format('Y-m-d')])
            ->fetchAllAssociative();
    }
}

九、查询缓存 #

9.1 启用缓存 #

php

<?php

class UserRepository extends ServiceEntityRepository
{
    public function findActiveCached(): array
    {
        return $this->createQueryBuilder('u')
            ->where('u.active = :active')
            ->setParameter('active', true)
            ->getQuery()
            ->enableResultCache(3600, 'active_users')
            ->getResult();
    }

    public function clearCache(): void
    {
        $this->getEntityManager()
            ->getConfiguration()
            ->getResultCache()
            ->delete('active_users');
    }
}

十、总结 #

本章学习了：

QueryBuilder基础
SELECT查询
WHERE条件构建
JOIN关联查询
排序和分页
DQL查询语言
子查询
原生SQL
查询缓存

下一章将学习关联关系。