CODEWITHSUNDEEP
phpdoctrine-ormdoctrine
FreeLightman
FreeLightman

Reputation: 2304

How to query ManyToMany relationship in Doctrine where association id not equal

I did see this question and event thought that it was what I needed. It it quite similar to my requirements. However chaning a little thing appeared to break the whole logic.

So here is my schema:

/** @Entity **/
class Product
{    
    /**
     * @ManyToMany(targetEntity="Category", inversedBy="products")
     **/
    private $categories;

    public function __construct() {
        $this->categories = new \Doctrine\Common\Collections\ArrayCollection();
    }

    // ...
}

/** @Entity **/
class Category
{
    /**
     * @ManyToMany(targetEntity="Product", mappedBy="categories")
     **/
    private $products;

    public function __construct() {
        $this->products = new \Doctrine\Common\Collections\ArrayCollection();
    }
}

I need to query all products which do not belong to a specific category. My try is:

// ProductRepository

public function getProducts() {
        return $this->createQueryBuilder('p')
            ->innerJoin('p.categories', 'c')
            ->where('c.id != :id')
            ->setParameter('id', 1)
            ->getQuery()
            ->getResult();
    }

I do not post here my data because the result of this query is all products. I have about 5 000 products and quite sure that not all of them belong to my category. I tried different categories. It shows all products not depending on my query.

I am also leaving SQL query here:

SELECT p0_.* FROM products p0_ 
INNER JOIN product_categories p2_ ON p0_.id = p2_.product_id
INNER JOIN categories c1_ ON c1_.id = p2_.category_id 
WHERE c1_.id != 1

Another problem is that it gives me products duplications. I can use group by p0_.id but I do not know if it efficient way.

I read google info about many-to-many relations and how to query them but all I found it about equality query and I have non equality.

Upvotes: 1

Views: 785

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

To exclude all products which belongs to category 1, you could use not in expression in doctrine as

$categoryProducts = $this->createQueryBuilder('p1')
                         ->select('p1.id')
                         ->innerJoin('p1.categories', 'c')
                         ->where('c.id = :id')
                         ->setParameter('id', 1)
                         ->getDQL();
$qb =  $this->createQueryBuilder('p');
$products = $qb->where(
                $qb->expr()->not(
                    $qb->expr()->in(
                        'p.id',
                        $categoryProducts
                    )
                )
            )
            ->getQuery()
            ->getResult();

Another approach but not sure, i guess this should produce same results by using a left join query

$this->createQueryBuilder('p')
    ->leftJoin('p.categories', 'c', 'WITH', 'c.id = :id')
    ->where('c.id IS NULL' )
    ->setParameters('id',1)
    ->getQuery()
    ->getResult();

Upvotes: 1

Related Questions