Use like in the findby method to filter records?

Question

I have the following code in my controller:

$shares = $this
    ->getDoctrine()
    ->getRepository('AppBundle:Shares')
    ->findBy(
        $where,
        $orderBy,
        $paginator->getPerPage(),
        $paginator->getCursor()
    )
;

Note that I have as a parameter the variable where for the method findby.

The variable where is an array:

$where['hashtags'] = "teste";

The problem is that in the entity Shares, the field hashtags is an array:

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

In the database is saved as follows:

a:2:{i:0;s:5:"teste";i:1;s:9:"bubbletea";}

How do I search all the records you have in the field hashtags the tag "test" using the function findby?

Answer 1

I have worked with a system similar to yours (in which I recorded a value vector in the database through Doctrine, which in turn stored it as a serialized vector), and at the end of the accounts I had to normalize the tag table.

Why? Because the search in the tag column performed very poorly - after all, we are searching for a string that can be located anywhere in the string.

If you want to take advantage of the solution, I believe the solution is something like:

$shares = $this
    ->getDoctrine()
    ->getManager()
    ->createQuery('
        SELECT s
        FROM AppBundle:Shares s
        WHERE s.hashtags LIKE :hashtag')
    ->setParameter('hashtag', '%"' . $hashtag . '"%')
    ->setMaxResults($paginator->getPerPage())
    ->setFirstResult($paginator->getCursor())
    ->getResult();

Now, if you prefer (and can) to switch to a more performative approach, I would create an n-to-n relationship between shares and hashtags, and search the hashtag table to find out what shares have those hashtags.

With this approach, you can still create an index in the hashtags column, which would make the search even faster.