Case insensitive search using findoneby ()

Question

I have one repository called $colorMaster

 $colorMaster->findOneBy(array("name"=>'RED'); 

It does not return any records, although there are records in my database with a value of "Red".

when I try to use the same code with the value "Red"

 $colorMaster->findOneBy(array("name"=>'RED'); 

it returns a value.

Is there a way by which we can get case-sensitive entries from symfony findOneBy(); so that it can also produce results using the value "RED".

Answer 1

The problem is not at the Symfony level, but at the level of your database. Some of them are case sensitive and some are not. Apparently your case sensitivity. To get the results, regardless of whether, follow these steps:

  $name = 'REd'; //case doesn't matter $colorMaster->createQueryBuilder('a') ->where('upper(a.name) = upper(:name)') ->setParameter('name', $name) ->getQuery() ->execute(); 

Answer 2

The accepted answer did not work for me, presumably due to case insensitivity.

 $name = 'REd'; $qb = $this->createQueryBuilder('a'); // still returns case insensitive results $qb->where($qb->expr()->upper('a.name'), $qb->expr()->upper(':name')) ->setParameter('name', $name, Type::STRING); // doctrine syntax error $name = 'REd'; $qb = $this->createQueryBuilder('a'); $qb->where('BINARY a.name = :name') ->setParameter('name', $name, Type::STRING); // this finally works $name = 'REd'; $rsm = new ResultSetMappingBuilder($this->_em); $rsm->addRootEntityFromClassMetadata('UserBundle:User', 'u'); $query = $this->_em->createNativeQuery('SELECT u.* FROM users u WHERE u.name LIKE BINARY :name', $rsm); $query->setParameters(array('name' => $name));