Can't found the FIND_IN_SET method in Symfony

Question

In my repository class. I did a specific query ro retrieve the data from database. I have a column with a content like this 44,55,22. So, I'am using the FIND_IN_SET method to find a specific number in a set.

I tried to transpose this in Symfony

$query->orWhere($query->expr()->andX('f.private = :private',"FIND_IN_SET({$user->getId()},'f.toUsers')"));

But I got this Symfony error which means that the method is absent.

Uncaught PHP Exception Doctrine\ORM\Query\QueryException: "[Syntax Error] line 0, col 821: Error: Expected known function, got 'FIND_IN_SET'"

Is the content of your field a list of foreign keys serialized? — Cid, Jan 07 '19 at 09:15
May be related to https://stackoverflow.com/questions/36246126/doctrine-find-in-set-leads-to-error-expected-end-of-string-got — Jules R, Jan 07 '19 at 09:15
@JulesR. The response to this question is not really adapted to mine. Thanks — KubiRoazhon, Jan 07 '19 at 09:19
This still looks like a badly designed table. You should consider normalisation. — Cid, Jan 07 '19 at 09:21

score 2 · Answer 1 · answered Jan 07 '19 at 12:46

SOLUTION

Here is the solution to this problem for anyone who confronts it.

First, you should add this line to the config.yml file

orm:
  dql:
    string_functions:
      FIND_IN_SET: DoctrineExtensions\Query\Mysql\FindInSet

This is required for Symfony to know where function is located.

In your repository class you can call the function this way (it is just an example) $query->OrWhere("FIND_IN_SET({$user->getId()},'55') <> 0");

Can't found the FIND_IN_SET method in Symfony

1 Answers1