Symfony2-Doctrine -- Selecting data with WHERE from DoctrineArray

Question

In an entity I have a field that looks like this:

/**
 * @ORM\Column(type="array")
 */
protected $category;

and QueryBuilder

$qb = $this->createQueryBuilder('s');
$qb->select($fields)
    ->where( 's.category IN (:category)') //////////// <----
    ->orderBy('s.name', 'ASC')
    ->setParameter('category', $category_id);

So in database field category is Doctrine2 Array. I want to select records from database with QueryBuilder. My question is, how can i do this, with WHERE clause that will be checking fields from that array ?

Nope. You can't query on array values. If you check your database you will see it's actually stored as a string. The array type is a doctrine extension for persisting arrays. The database itself does not know anything about them. If you want to do this kind of querying then you need to make an entity called Category with it's own table and associate it with your main entity. — Cerad, Aug 20 '13 at 15:59

score 0 · Answer 1 · answered Aug 27 '13 at 07:32

0

A look here may help you

// Instead, use $qb->expr()->in('value', array('?1')) and bind your parameter to ?1 (see section above)
public function in($x, $y); // Returns Expr\Func instance

answered Aug 27 '13 at 07:32

Thierry

746
13
15

score 0 · Answer 2 · answered Aug 27 '13 at 09:30

0

$qb->select($fields)
  ->where($qb->expr()->in('s.category', $categories))

answered Aug 27 '13 at 09:30

Tib

2,553
1
27
46

score 0 · Answer 3 · edited May 23 '17 at 11:46

@Cerad gave you a perfectly valid comment. One of the problem of storing arrays is that you don't have any chance of searching.

See PHP/MySQL - Storing array in database, and Storing arrays in the database. As you can see, it is a terrible practice.

The best way is to simply create a Category entity, and to have a OneToMany relation with that category.

Here is an example of an entity Book that has many categories:

1 Create your category entity:

class Category implements CategoryInterface
{
    //.....

    /**
     * Title of the category
     *
     * @ORM\Column(type="string", length=100)
     */
    protected $title;

    /**
     * Relation with your book entity for example
     *
     * @ORM\ManyToOne(targetEntity="Book", inversedBy="categories")
     * @ORM\JoinColumn(name="book_id", referencedColumnName="id")
     */
    private $book;

    /**
     * Set book
     *
     * @param BookInterface $book
     */
    public function setBook(BookInterface $book)
    {
        $this->book = $book;
    }

    /**
     * Get book
     *
     * @return BookInterface
     */
    public function getBook()
    {
        return $this->book;
    }


}

2 Your book entity:

use Doctrine\Common\Collections\ArrayCollection;

class Book implements BookInterface
{
    /**
     * Categories for the books
     *
     * @ORM\OneToMany(targetEntity="Category", mappedBy="book")
     * @var CategoryInterface[]
     */
    protected $categories ; 

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

   /**
     * Add Categories
     *
     * @param CategoryInterface $category
     */
    public function addCategory(CategoryInterface $category)
    {
        $category->setBook($this);
        $this->categories->add($category);
    }

    /**
     * Remove Category
     *
     * @param CategoryInterface $category
     * @return bool
     */
    public function removeCategory(CategoryInterface $category)
    {
        return $this->categories->removeElement($category);
    }

    /**
     * Get Categories
     *
     * @return Doctrine\Common\Collections\Collection
     */
    public function getCategories()
    {
        return $this->categories;
    }

    /**
     * Set Categories
     *
     * @param ArrayCollection $categories
     */
    public function setCategories($categories) {

        $this->categories->clear();

        foreach ($categories as $category) {
            $this->addCategory($category);
        }

        return $this;
    }

3 Your can now search properly.

Symfony2-Doctrine -- Selecting data with WHERE from DoctrineArray

3 Answers3