Doctrine ORM教程：通过关联实体字段数组高效筛选数据

// app/src/Entity/Listing.php
namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass=ListingRepository::class)
 * @ORM\Table(name="listings", schema="app")
 */
class Listing
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private int $id;

    /** @ORM\Column(type="string", length=255, nullable=false) */
    private string $name;

    /** @ORM\ManyToOne(targetEntity=City::class, inversedBy="listings") */
    private City $city;

    // ... 其他字段和方法
}

// app/src/Entity/City.php
namespace App\Entity;

use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass=CityRepository::class)
 * @ORM\Table(name="cities", schema="app")
 */
class City
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private int $id;

    /** @ORM\Column(type="string", length=255, nullable=false) */
    private string $name;

    /** @ORM\Column(type="string", length=255, nullable=false) */
    private string $slug;

    /** @ORM\OneToMany(targetEntity=Listing::class, mappedBy="city") */
    private Collection $listings;

    // ... 其他字段和方法
}

// 在ListingRepository中
use Doctrine\DBAL\Connection;

class ListingRepository extends ServiceEntityRepository
{
    // ... 构造函数等

    /**
     * @param City[] $cities
     * @return Listing[]
     */
    public function findAllByCitiesInefficiently(array $cities): array
    {
        // 步骤2: 提取城市ID
        $citiesIds = array_map(static fn(City $city): int => $city->getId(), $cities);

        // 步骤3: 使用城市ID查询Listing
        $qb = $this->createQueryBuilder('l');
        return $qb
            ->select('l')
            ->where($qb->expr()->in('l.city', ':cities'))
            ->setParameter('cities', $citiesIds, Connection::PARAM_STR_ARRAY)
            ->getQuery()
            ->getResult();
    }

    // 假设在某个服务或控制器中先获取城市实体
    // public function someServiceMethod(array $citySlugs) {
    //     $cities = $this->cityRepository->findBy(['slug' => $citySlugs]); // 步骤1
    //     return $this->listingRepository->findAllByCitiesInefficiently($cities);
    // }
}

// app/src/Repository/ListingRepository.php
namespace App\Repository;

use App\Entity\Listing;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\DBAL\Connection; // 引入Connection类用于参数类型

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

    /**
     * 根据城市slug数组筛选所有列表项。
     *
     * @param array $citySlugs 城市slug的数组，例如 ['new_york', 'rome']
     * @return Listing[] 返回符合条件的Listing实体数组
     */
    public function findAllByCitySlugs(array $citySlugs): array
    {
        $qb = $this->createQueryBuilder('l'); // 为Listing实体创建查询构建器，并指定别名'l'

        return $qb
            ->select('l') // 选择Listing实体
            ->join('l.city', 'c') // 关键步骤：通过Listing的'city'属性关联到City实体，并指定别名'c'
            ->where($qb->expr()->in('c.slug', ':citySlugs')) // 在关联的City实体（别名'c'）的'slug'字段上使用IN表达式
            ->setParameter('citySlugs', $citySlugs, Connection::PARAM_STR_ARRAY) // 绑定参数，使用PARAM_STR_ARRAY指示这是一个字符串数组
            ->getQuery() // 获取DQL查询对象
            ->getResult(); // 执行查询并返回结果
    }
}