Use the like in findby method to filter records?

Question

Use the like in findby method to filter records?

Navigation

#1 by (1 votes)

1

I have the following code in my controller:

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

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

The variable where is an array:

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

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

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

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 that have in the hashtags field the "test" tag using the hashtags function?

symfony-2 doctrine

asked by anonymous 10.05.2016 / 16:10

1 answer

I can not clone a git repository in TFS on my local machine Form for relationship ForeignKey

score 1 · Accepted Answer

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

Why? Because the search in the column of tags was very bad - 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) switch to a more performative approach, I would create an n-to-n relationship between the shares and the hashtags, and search the hashtags table to find out which shares they own those hashtags.

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