Doctrine2 find by value in field array

Question

i wonder if there is a way to search for a document field looking like :

/**
 * @var array
 *
 * @ORM\Column(name="tags", type="array", nullable=true)
 */
private $tags;

which in database looks like php array interpretation :

a:3:{i:0;s:6:"tagOne";i:1;s:6:"tagTwo";i:2;s:8:"tagThree";}

now i try to search the entity by a tag tryed

public function findByTag($tag) {
    $qb = $this->em->createQueryBuilder();
    $qb->select('u')
        ->from("myBundle:Entity", 'u')
        ->where('u.tags LIKE :tag')
        ->setParameter('tag', $tag );
    $result=$qb->getQuery()->getResult();
    return $result;
}

which always returns array[0] just dont get it

i am able to change the way how they are saved for any help, thanks in advance

Answer 1

Based on previous answers and one thought expressed in a comment of mine, I decided to use this general static function to do the job:

/**
     * @param EntityManagerInterface $entityManager
     * @param string                 $entity
     * @param string                 $arrayField
     * @param string                 $string
     *
     * @return array
     */
    public static function findByStringInArrayField(
        EntityManagerInterface $entityManager,
        string $entity,
        string $arrayField,
        string $string
    ): array {
        $serializedString = serialize($string);
        $columnName       = $entityManager->getClassMetadata($entity)->getColumnName($arrayField);
        $qb               = $entityManager->createQueryBuilder();

        return $qb->select('u')
                  ->from(GNode::class, 'u')
                  ->where(
                      $qb->expr()
                         ->like(
                             "u.$columnName",
                             $qb->expr()->literal("%$serializedString%")
                         )
                  )
                  ->getQuery()
                  ->getResult();
    }

…and would call it from within a ServiceEntityRepository like that:

$entities = self::findByStringInArrayField($this->getEntityManager(), MyEntity::class, 'tags', $tag);

Answer 2

You need to define a literal tag for % before and/or after the value you want to search; in this case you won't even need to have single quotation before and after your phrase:

$qb = $this->em->createQueryBuilder();
$qb->select('u')
    ->from("myBundle:Entity", 'u')
    ->where($qb->expr()->like('u.tags', $qb->expr()->literal("%$tag%")))
$result=$qb->getQuery()->getResult();
return $result;

You can follow a list of all Doctrine expr class

Answer 3

I achieved this few months ago - you're missing the % wildcards. You could do the following:

$qb->select('u')
    ->from("myBundle:Entity", 'u')
    ->where('u.tags LIKE :tag')
    ->setParameter('tag', '%"' . $tag . '"%' );

The critical part, obviously, is placing % wildcards, but you would also need to put " (double quotes) for preventing the selection of partial matches (if necessary). Leave those out to include partials as well but since you're searching for the tags I don't think that's the case.

Hope this helps...