Symfony 2, QueryBuilder, multiple andWhere with same parameter

Question

I have two entities - Lawyer and Category. They are connected with Many to Many association. Assume that example lawyer has 3 categories. I want to create function to search lawyers by a array of categories, and it should return only lawyers who have all categories from array.

class Lawyer {
    //...
    /**
     * @ORM\ManyToMany(targetEntity="Dees\KancelariaBundle\Entity\Category")
     * @ORM\JoinTable(name="lawyers_has_categories",
     *      joinColumns={@ORM\JoinColumn(name="lawyer_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="category_id", referencedColumnName="id")}
     * )
     *
     * @var ArrayCollection
     */
    protected $categories = null;   
    //...
}

class Category {
    //...
    /**
     * @ORM\Column(length=255, nullable=true)
     *
     * @var string
     */
    protected $name;    
    //...   
}

public function searchLawyers(array $categories) {
    $queryBuilder = $this->createQueryBuilder('lawyer')
            ->join('lawyer.categories', 'category');

    $queryBuilder->andWhere("category.name = :category1");
    $queryBuilder->setParameter("category1", "First category");     

    $queryBuilder->andWhere("category.name = :category2");
    $queryBuilder->setParameter("category2", "Second category");
    //...       
    //won't work, return null, but lawyer with these categories exists.
}

How can I achieve something like that?

dees91 · Accepted Answer · 2014-12-04 15:21:05Z

7

I figured it out:

public function searchLawyers(array $categories) {
    $queryBuilder = $this->createQueryBuilder('lawyer')
        ->join('lawyer.categories', 'category');

        $queryBuilder->andWhere("category.name in (:categories)");
        $queryBuilder->setParameter("categories", $categories);

        $queryBuilder->addGroupBy("lawyer.id");

        $queryBuilder->andHaving("COUNT(DISTINCT category.name) = :count");
        $queryBuilder->setParameter("count", sizeof($categories));

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

answered Dec 4, 2014 at 15:21

dees91

1,46115 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Symfony 2, QueryBuilder, multiple andWhere with same parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related