Doctrine ORM 高效查询：利用关联实体字段数组（IN 子句）筛选数据

// ListingRepository.php
use Doctrine\DBAL\Connection;
use App\Entity\City;
use App\Entity\Listing;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

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

    /**
     * @param City[] $cities
     * @return Listing[]
     */
    public function findAllByCities(array $cities): array
    {
        $qb = $this->createQueryBuilder('l');
        $citiesIds = $this->getCitiesIds($cities); // 获取城市ID数组

        if (empty($citiesIds)) {
            return []; // 如果没有城市ID，则返回空数组
        }

        return $qb
            ->select('l')
            ->where($qb->expr()->in('l.city', ':cities'))
            ->setParameter('cities', $citiesIds, Connection::PARAM_INT_ARRAY) // 注意这里应为PARAM_INT_ARRAY
            ->getQuery()
            ->getResult();
    }

    /**
     * @param City[] $cities
     * @return int[]
     */
    private function getCitiesIds(array $cities): array
    {
        return array_map(static fn(City $city): int => $city->getId(), $cities);
    }

    // ... 其他方法
}

// 在某个服务或控制器中调用：
// 假设 $citySlugs = ['new_york', 'rome'];
// $cities = $cityRepository->findBy(['slug' => $citySlugs]); // 先查询城市
// $listings = $listingRepository->findAllByCities($cities);

// ListingRepository.php
use Doctrine\DBAL\Connection;
use App\Entity\Listing;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

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

    /**
     * 根据城市slug数组查询所有相关的Listing实体。
     *
     * @param array $citySlugs 包含城市slug字符串的数组。
     * @return Listing[] 返回符合条件的Listing实体数组。
     */
    public function findAllByCitySlugs(array $citySlugs): array
    {
        $qb = $this->createQueryBuilder('l');

        if (empty($citySlugs)) {
            return []; // 如果城市slug数组为空，则直接返回空数组
        }

        return $qb
            ->select('l') // 选择Listing实体
            ->join('l.city', 'c') // 连接Listing的city属性，并为其设置别名'c'
            ->where($qb->expr()->in('c.slug', ':citySlugs')) // 在关联实体的slug字段上使用IN子句
            ->setParameter('citySlugs', $citySlugs, Connection::PARAM_STR_ARRAY) // 绑定参数，指定为字符串数组类型
            ->getQuery()
            ->getResult();
    }

    // ... 其他方法
}