Check if an array contains any element of another array in doctrine query builder

Question

I would like to know if it's possible to check if an array contains any element of another array in doctrine query builder.

In my case, i want to get all the products (items) who have at least one of the category in the array passed in parameter.

Relationship between Item and Category :

/**
 *  @ORM\ManyToMany(targetEntity="Category")
 *  @ORM\JoinTable(name="items_categories",
 *      joinColumns={@ORM\JoinColumn(name="item_id", referencedColumnName="id", nullable=false)},
 *      inverseJoinColumns={@ORM\JoinColumn(name="category_id", referencedColumnName="id", nullable=false)}
 *      )
 */
private $categories;

My first try from the Item repository (i know this work if i have only one value to check):

public function getListItemsFromCatList($listCat) {
    $qb = $this->createQueryBuilder('i');

    $qb->select('i')
            ->where($qb->expr()->like('i.categories', ':listCat'))
            ->setParameter('listCat', '%"' . $listCat . '"%');

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

$listCat is an array of Category Entity :

array (size=5)
  0 => 
    object(ItemBundle\Entity\Category)[518]
      private 'id' => int 22
      private 'children' => 
        object(Doctrine\ORM\PersistentCollection)[520]
          private 'snapshot' => 
            array (size=2)
              ...
          private 'owner' => 
            &object(ItemBundle\Entity\Category)[518]
          private 'association' => 
            array (size=15)
              ...
          private 'em' => 
            object(Doctrine\ORM\EntityManager)[796]
              ...
          private 'backRefFieldName' => string 'parent' (length=6)
          private 'typeClass' => 
            object(Doctrine\ORM\Mapping\ClassMetadata)[579]
              ...
          private 'isDirty' => boolean false
          protected 'collection' => 
            object(Doctrine\Common\Collections\ArrayCollection)[515]
              ...
          protected 'initialized' => boolean true
      private 'parent' => null
      private 'name' => string 'Luxe' (length=4)
  1 => 
    object(ItemBundle\Entity\Category)[504]
      private 'id' => int 25
      private 'children' => 
        object(Doctrine\ORM\PersistentCollection)[505]
          private 'snapshot' => 
            array (size=0)
              ...
          private 'owner' => 
            &object(ItemBundle\Entity\Category)[504]
          private 'association' => 
            array (size=15)
              ...
          private 'em' => 
            object(Doctrine\ORM\EntityManager)[796]
              ...
          private 'backRefFieldName' => string 'parent' (length=6)
          private 'typeClass' => 
            object(Doctrine\ORM\Mapping\ClassMetadata)[579]
              ...
          private 'isDirty' => boolean false
          protected 'collection' => 
            object(Doctrine\Common\Collections\ArrayCollection)[500]
              ...
          protected 'initialized' => boolean false
      private 'parent' => 
        object(ItemBundle\Entity\Category)[512]
          private 'id' => int 23
          private 'children' => 
            object(Doctrine\ORM\PersistentCollection)[513]
              ...
          private 'parent' => 
            object(ItemBundle\Entity\Category)[518]
              ...
          private 'name' => string 'Bijoux' (length=6)
      private 'name' => string 'Bagues' (length=6)

See my edit #2 which I think might work.

Alvin Bunk
– Alvin Bunk

2017-01-16 22:36:16 +00:00
Commented Jan 16, 2017 at 22:36 — Alvin Bunk
– Alvin Bunk, Commented Jan 16, 2017 at 22:36

Vinícius Fagundes · Accepted Answer · 2017-01-17 02:20:47Z

13

I would solve it adding a join.

public function getListItemsFromCatList($listCat) {

    $em = $this->getDoctrine()->getManager();

    $qb = $em->createQueryBuilder();
    $qb->select('i')           
        ->from('AppBundle:Item', 'i')
        ->innerJoin('i.categories','cat')
        ->where('cat IN (:listCat)')
       ->setParameter('listCat', $listCat);

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

Notice this approach will filter the categories inside items. It means that when you try to get categories from a given item i, $i->getCategories(), it will return only the categories from i which matchs with $listCat.

If you will need to use all categories from each item, even those doesn't match with $listCat. I will recommend you to use subqueries to filter, and main query to return the full items. Let a comment if you need any futher help with that.

answered Jan 17, 2017 at 2:20

Vinícius Fagundes

2,05116 silver badges25 bronze badges

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

1 Comment

LedZelkin Over a year ago

In my case just have the category which match with $listCat is OK. So Thank you for this answer

Alvin Bunk · Accepted Answer · 2017-01-16 22:49:14Z

1

Try your query builder like this instead:

$em = $this->getDoctrine()->getManager();
$qb = $em->createQueryBuilder();
$qb->select('i')
    ->from('AppBundle:Item', 'i')
    ->where('i.categories LIKE :listCat')
    ->setParameter('listCat', '%"' . $listCat . '"%');

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

I think that should work. I'm presuming the Entity in Doctrine you are using is called Item.

EDIT #2 - based on comments

You need to loop through the array and get the name (I think), which I think is the category you are looking for. In this code I show looping through to create a $cats variable, and then use that in a IN query.

foreach ($listCat as $item){
    $cats = $cats . "'" . $item.getName() . "'".',';
}
$cats = substr($cats, -1);


$em = $this->getDoctrine()->getManager();
$qb = $em->createQueryBuilder();
$qb->select('i')
    ->from('AppBundle:Item', 'i')
    ->where("i.categories IN (:listCat)")
    ->setParameter('listCat', $cats);

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

Can you try it? I think you need something like that.

edited Jan 16, 2017 at 22:49

answered Jan 16, 2017 at 21:44

Alvin Bunk

7,8123 gold badges31 silver badges48 bronze badges

8 Comments

LedZelkin Over a year ago

This is the same as my test. For information, $listcat is an array of Category Entity. So this can't Work ( i just tried and i get the following error : Notice: Array to string conversion)

Alvin Bunk Over a year ago

Is $listCat an array?

Alvin Bunk Over a year ago

Please show an example of what $listCat contains. Maybe you can use IN instead?

LedZelkin Over a year ago

Done, but i don't think this will work because i.categories is a collection too. What would be great, would to have the behavior on this post

LedZelkin Over a year ago

I get this error :

[Semantical Error] line 0, col 47 near 'categories IN': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

I think it's normal because the IN clause is waiting for only one value on left side (i.categories)

|

Collectives™ on Stack Overflow

Check if an array contains any element of another array in doctrine query builder

2 Answers 2

1 Comment

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related