Symfony Query Builder 中多对多关系实现“与”条件查询教程

public function findByAttributesOr(array $attributesSlugs)
{
    $qb = $this->createQueryBuilder('p')
        ->join('p.attributes', 'a');

    $orConditions = $qb->expr()->orX();
    foreach ($attributesSlugs as $i => $slug) {
        $orConditions->add($qb->expr()->eq('a.slug', ':slug'.$i));
        $qb->setParameter('slug'.$i, $slug);
    }
    $qb->where($orConditions);

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

// 错误的示例：尝试直接使用 AND
public function findByAttributesAndIncorrect($attributesSlugs)
{
    $qb = $this->createQueryBuilder('p')
        ->join('p.attributes', 'a')
        ->where('a.slug = :slug1 AND a.slug = :slug2') // 错误用法
        ->setParameter('slug1', $attributesSlugs[0])
        ->setParameter('slug2', $attributesSlugs[1]);

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

use Doctrine\ORM\EntityRepository;

class ProductRepository extends EntityRepository
{
    /**
     * 查找同时拥有所有指定属性的产品。
     *
     * @param array $attributeSlugs 属性slug数组，例如 ['red', 'blue']
     * @return array
     */
    public function findByAttributes(array $attributeSlugs): array
    {
        $qb = $this->createQueryBuilder('p');

        foreach ($attributeSlugs as $i => $slug) {
            // 关键：每次循环都创建一个新的别名来连接 p.attributes
            // 例如：第一次循环连接为 'a0'，第二次为 'a1'，以此类推
            $qb->join('p.attributes', 'a'.$i)
               // 对每个独立的连接应用其特定的 slug 条件
               ->andWhere('a'.$i.'.slug = :slug'.$i)
               // 绑定参数，确保查询安全
               ->setParameter('slug'.$i, $slug);
        }

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

<?php

namespace App\Repository;

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

/**
 * @extends ServiceEntityRepository<Product>
 *
 * @method Product|null find($id, $lockMode = null, $lockVersion = null)
 * @method Product|null findOneBy(array $criteria, array $orderBy = null)
 * @method Product[]    findAll()
 * @method Product[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class ProductRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Product::class);
    }

    /**
     * 查找同时拥有所有指定属性的产品。
     *
     * @param array $attributeSlugs 属性slug数组，例如 ['red', 'blue']
     * @return Product[]
     */
    public function findByAttributes(array $attributeSlugs): array
    {
        if (empty($attributeSlugs)) {
            return []; // 如果没有指定属性，则返回空数组或根据业务逻辑返回所有产品
        }

        $qb = $this->createQueryBuilder('p');

        foreach ($attributeSlugs as $i => $slug) {
            $qb->join('p.attributes', 'a'.$i)
               ->andWhere('a'.$i.'.slug = :slug'.$i)
               ->setParameter('slug'.$i, $slug);
        }

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

<?php

namespace App\Controller;

use App\Repository\ProductRepository;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;

class ProductController extends AbstractController
{
    #[Route('/products/filter', name: 'app_products_filter')]
    public function filterProducts(ProductRepository $productRepository): Response
    {
        // 查找同时拥有 'red' 和 'blue' 属性的产品
        $products = $productRepository->findByAttributes(['red', 'blue']);

        // 查找同时拥有 'large' 和 'cotton' 属性的产品
        // $products = $productRepository->findByAttributes(['large', 'cotton']);

        // ... 处理 $products 数组 ...

        return $this->render('product/filtered_list.html.twig', [
            'products' => $products,
        ]);
    }
}