Use like in the findby method to filter records?

Asked

Viewed 290 times

1

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?

1 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.

  • Actually the idea of serializing was not the best one even more because the whole application revolves around tags, however the solution worked. I will change and add a Manytomany relation. Thanks for the help.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.