学说多对多 - 产品和类别

Question

How can I build the search query to search products using category ? Following is my category and product.

This is my product entity

class Product {
    /**
     * @ORM\Id
     * @ORM\Column(type="integer", nullable=false)
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    protected $id;

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


    /**
     * @var \Doctrine\Common\Collections\Collection|UserGroup[]
     *
     * @ORM\ManyToMany(targetEntity="Catalog\Model\Entity\Category", inversedBy="product")
     * @ORM\JoinTable(
     *  name="product_category",
     *  joinColumns={
     * @ORM\JoinColumn(name="product_id", referencedColumnName="id")
     *  },
     *  inverseJoinColumns={
     * @ORM\JoinColumn(name="category_id", referencedColumnName="id")
     *  }
     * )
     */
    protected $categories;

}

and This is category entity..

class Category {

    /**
     * @ORM\Id
     * @ORM\Column(type="integer", nullable=false)
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    protected $id;
    /**
     * @ORM\Column(type="string", length=255, nullable=false)
     */
    protected $title;

    /**
     * @ORM\ManyToOne(targetEntity="Catalog\Model\Entity\Product", inversedBy="categories")
     */
    /**
     * @var \Doctrine\Common\Collections\Collection|Product[]
     *
     * @ORM\ManyToMany(targetEntity="Catalog\Model\Entity\Product", mappedBy="categories")
     */
    protected $products;
} 

and I am trying to build search query. How I should modify the following query to search using category ?

    $productName = $searchParams['productName'];

    $arrWhere = array();
    $productSql = "SELECT p FROM \Catalog\Model\Entity\Product p";

    if ($productName) {
        array_push($arrWhere, "p.name LIKE '" . '%'.$productName.'%' . "'");
    }


    if (count($arrWhere) > 0) {
        $productSql.= " WHERE " . implode(' AND ',$arrWhere);
    }

    $productSql.= $order_query_str;

    $query = $em->createQuery($productSql);

Answer 1

I'd suggest using Doctrine's Query builder.

$qb = $em->createQueryBuilder();
$qb->select(‘p’)
    -> from('\Catalog\Model\Entity\Product')
    ->leftJoin('p.categories', 'c');


if (isset($searchParams['productName'])) {
    $qb->andWhere('p.name LIKE :prodName')
        ->setParameter('prodName', '%' . $searchParams['productName'] . '%');
}
if (isset($searchParams['categoryTitle'])) {
    $qb->andWhere('c.title LIKE :catTitle')
        ->setParameter('catTitle', '%' . $searchParams['categoryTitle'] . '%');
}

$query = $qb->getQuery();