Symfony Doctrine query builder LIKE not working in PostgreSQL

Question

So I have a UserRepository that contains the following code

namespace AppBundle\Repository;
use \Doctrine\ORM\EntityRepository;
class UserRepository extends EntityRepository
{
    public function findByRole($role)
    {
        $qb = $this->_em->createQueryBuilder();
        $qb->select('u')
            ->from($this->_entityName, 'u')
            ->where('u.roles LIKE :roles')
            ->setParameter('roles', '%"'.$role.'"%');

        return $qb->getQuery()->getResult();
    }
}

This seems to be working perfectly fine if my database is MySQL but if i change the database to PostgreSQL this query throws the following error

An exception occurred while executing 'SELECT p0_.id AS id_0, p0_.username AS username_1, p0_.password AS password_2, p0_.is_active AS is_active_3, p0_.roles AS roles_4, p0_.name AS name_5, p0_.street AS street_6, p0_.city AS city_7, p0_.state AS state_8, p0_.zip_code AS zip_code_9, p0_.phone_number AS phone_number_10, p0_.dob AS dob_11, p0_.company_name AS company_name_12, p0_.company_slug AS company_slug_13, p0_.company_logo AS company_logo_14, p0_.company_details AS company_details_15, p0_.stripe_customer_id AS stripe_customer_id_16, p0_.created_at AS created_at_17, p0_.updated_at AS updated_at_18 FROM px_user p0_ WHERE p0_.roles LIKE ?' with params ["%\"ROLE_EMPLOYER\"%"]:

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json ~~ unknown LINE 1: ...at AS updated_at_18 FROM px_user p0_ WHERE p0_.roles LIKE $1 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

This is the first time I am working with PostgreSQL so I am not getting what the problem is. After playing around with it for a while if I change the generated query to by adding the following piece

WHERE 
  p0_.roles::text LIKE '%ROLE_EMPLOYER%'

Everything works fine. Note the ::text.

So now how can i add that to the query builder so it works with PostgreSQL as well.

->where('CAST(u.roles AS TEXT) LIKE :roles') should work. Your roles is a json typed column and such cannot be used instead of string types directly (where the engine expects a string type (f.ex. text) -- the LIKE operator (also ~~) is just like that). — pozs
– pozs, Commented Dec 21, 2016 at 13:54
@pozs thanks for sharing the information, I did not know that. After making the change I am seeing [Syntax Error] line 0, col 44: Error: Expected known function, got 'CAST' — Shairyar
– Shairyar, Commented Dec 21, 2016 at 14:00
@Baig then it seems Doctrine parses what you gave to where() and disallow some pretty basic SQL too. It's not even supported in DQL. As a worst case, you could use native queries. — pozs
– pozs, Commented Dec 21, 2016 at 14:07

Lou Zito · Accepted Answer · 2020-02-19 11:10:42Z

4

I solved the problem with the module boldtrn/jsonb-bundle but it created an error depending on the version of Postgres used.

I also solved the issue without the module by a native query like this :

public function findEmailsByRole($role)
{
    return $this->_em->getConnection()->executeQuery(
        "SELECT email FROM public.utilisateur WHERE roles::text LIKE :role",
        ['role'=>'%"' . $role . '"%']
    )->fetchAll();
}

answered Feb 19, 2020 at 11:10

Lou Zito

413 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barkati.med · Accepted Answer · 2016-12-21 15:11:59Z

2

You can create your Function Like this

PS: Im Working on PostgreSQL Too

    public function findByRole($role) {
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb->select('u')
            ->from($this->getEntityName(), 'u')
            ->where("u.roles LIKE '%$role%'")
    ;

    return $qb->getQuery()->getResult();
}

edited Dec 21, 2016 at 15:11

answered Dec 21, 2016 at 15:00

Barkati.med

6305 silver badges11 bronze badges

5 Comments

Shairyar Over a year ago

What are you trying to address? please elaborate

Barkati.med Over a year ago

Ok What its your Field Type In ORM

Shairyar Over a year ago

the role is database is saved as ["ROLE_USER","ROLE_EMPLOYER"]

Barkati.med Over a year ago

Okay well I'm Sorry but i have already got This problem and i don't find a good solution for it i can give you an alter solution its to create a new Fields Type Text and save a copy of your role Like This ROLE_USER,ROLE_EMPLOYER and use a simple DQL LIKE to find your Objects

Shairyar Over a year ago

I ended up fixing it, please see my answer. I hope that helps you out. I was tempted to add the field as you suggested but I did not want to do that as there had to be a solution to this.

Community · Accepted Answer · 2017-05-23 11:46:30Z

I solved the problem by using JsonbBundle.

Following steps I took to fix it

$ composer require "boldtrn/jsonb-bundle

Updated the config.yml by adding the following in its respective place.

doctrine:
    dbal:
        types:
          jsonb: Boldtrn\JsonbBundle\Types\JsonbArrayType
        mapping_types:
          jsonb: jsonb
    orm:
        dql:
            string_functions:
                JSONB_AG:   Boldtrn\JsonbBundle\Query\JsonbAtGreater
                JSONB_HGG:  Boldtrn\JsonbBundle\Query\JsonbHashGreaterGreater
                JSONB_EX:   Boldtrn\JsonbBundle\Query\JsonbExistence

Changed the roles property to type jsonb

And inside the repository the following query worked

$query = $this->getEntityManager()->createQuery("SELECT u FROM AppBundle:User u WHERE JSONB_HGG(u.roles , '{}') LIKE '%EMPLOYER%' ");
$users = $query->getResult();
return $users;

The credit goes to Doctrine query postgres json (contains) json_array

Kwido · Accepted Answer · 2016-12-21 14:51:51Z

0

public function findByRole($role)
{
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb->select('u')
        ->from($this->getEntityName(), 'u')
        ->where($qb->expr()->like('u.roles', ':roles')
        ->setParameter('roles', $qb->expr()->literal('%'.$role.'%'));

    return $qb->getQuery()->getResult();
}

Replaced your custom string DQL to the QueryBuilder syntax.

I don't know if it might be related to the syntax you've got within your like statement: '%".$var."%', which might bug it. Hope this helps you solve it.

Doctrine Querybuilder documentation like :

// Example - $qb->expr()->like('u.firstname', $qb->expr()->literal('Gui%'))
public function like($x, $y); // Returns Expr\Comparison instance

answered Dec 21, 2016 at 14:51

Kwido

1,3928 silver badges21 bronze badges

5 Comments

Shairyar Over a year ago

Thanks for having a look but this does not work, gives me the same error. While the query you and I wrote work with MySQL database but I am working on PostgreSQL and this fails.

Kwido Over a year ago

Okay well too bad, good luck on searching for the solution. If you found it I would like to see the (accepted) answer.

Shairyar Over a year ago

I ended up fixing the problem, please see my answer.

Kwido Over a year ago

Accept your own answer to mark the question as done.

Shairyar Over a year ago

Will do, it says I can accept it tomorrow. that was some crazy searching i did for 2 days.

Alvin Bunk · Accepted Answer · 2016-12-21 19:12:01Z

0

Looks like from the PostgreSQL documentation on LIKE, you might need to do this:

$qb = $this->_em->createQueryBuilder();
$qb->select('u')
    ->from($this->_entityName, 'u')
    ->where('u.roles LIKE :roles')
    ->setParameter('roles', '\'%'.$role.'%\'');

return $qb->getQuery()->getResult();

Essentially having single quotes outside the percent signs by escaping them. I'm not sure if that will work, but can you try it?

answered Dec 21, 2016 at 19:12

Alvin Bunk

7,8023 gold badges31 silver badges48 bronze badges

1 Comment

Shairyar Over a year ago

Thanks but that too does not work. throws the same error

pocketrocket · Accepted Answer · 2020-10-07 21:58:26Z

Building up on @Lou Zito answer:

If you are dealing with Postgres please learn the power of json/b operations!

Casting a json/b array into text for like comparison is not a good approach. Cast it into jsonb instead (or even better: change your setup / migration(s) to use jsonb fields directly instead <3)

You can use ? operator, or the contains operator @> as an example.

See this full list of jsonb operators available Please be aware, that ? will get interpreted as doctrine placeholder, you need to use ?? to escape it!

public function findByRole()
{
    return $query = $this->getEntityManager()
        ->getConnection()
        ->executeQuery(<<<'SQL'
            SELECT id FROM public.user
            WHERE roles::jsonb ?? :role
            ORDER BY last_name, first_name
SQL,
            ['role' => User::ROLE_XYZ]
        )->fetchAllAssociative();
}

Hint: As you can see in the example above, I usually extract roles as public string constants in the User entity for easy access. I strongly recommend that as best practice as well.

Collectives™ on Stack Overflow

Symfony Doctrine query builder LIKE not working in PostgreSQL

6 Answers 6

Comments

5 Comments

Comments

5 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

5 Comments

Comments

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related