How to do a LIKE database query in Symfony2

Question

This should be simple but I can't find a working example. Here's a controller method that throws the error "Invalid parameter number: number of bound variables does not match number of tokens". I'm posting the "searchterm" variable successfully but can't get the query to work. What is missing? Thanks!

 public function searchAction()
{
    $request = $this->getRequest();

    $searchterm = $request->get('searchterm');

    $em = $this->getDoctrine()->getEntityManager();

    $query = $em->createQuery("SELECT n FROM AcmeNodeBundle:Node n WHERE n.title LIKE '% :searchterm %'")
             ->setParameter('searchterm', $searchterm);

    $entities = $query->getResult();

    return array('entities' => $entities);

}

score 36 · Accepted Answer · answered Jun 15 '13 at 17:59

36

Working example from my Symfony2 project:

$qb = $this->createQueryBuilder('u');
$qb->where(
         $qb->expr()->like('u.username', ':user')
     )
     ->setParameter('user','%Andre%')
     ->getQuery()
     ->getResult();

answered Jun 15 '13 at 17:59

virtustilus

644
6
6

score 10 · Answer 2 · answered Sep 12 '11 at 06:08

You should dump the created query for easier debugging.

I can only suggest you also try the querybuilder:

$qb = $em->createQueryBuilder();
$result = $qb->select('n')->from('Acme\NodeBundle\Entity\Node', 'n')
  ->where( $qb->expr()->like('n.title', $qb->expr()->literal('%' . $searchterm . '%')) )
  ->getQuery()
  ->getResult();

doc

score 9 · Answer 3 · answered Aug 09 '17 at 11:41

9

I think this option also helps:

$qb = $this->createQueryBuilder('u');
$qb->where('u.username like :user')
     ->setParameter('user','%hereIsYourName%')
     ->getQuery()
     ->getResult();

answered Aug 09 '17 at 11:41

Imanali Mamadiev

2,604
2
15
23

score 2 · Answer 4 · answered Apr 30 '14 at 14:44

WHERE n.title LIKE '% :searchterm %'

should be

WHERE n.title LIKE :searchterm

public function searchAction() {
    $request = $this->getRequest();

    $searchterm = $request->get('searchterm');

    $em = $this->getDoctrine()->getEntityManager();

    $query = $em->createQuery("SELECT n FROM AcmeNodeBundle:Node n WHERE n.title LIKE :searchterm")->setParameter('searchterm', $searchterm);

    $entities = $query->getResult();

    return array('entities' => $entities);

}

score 0 · Answer 5 · answered Sep 12 '11 at 05:00

0

maybe AcmeNodeBundle\Node? In DQL AcmeNodeBundle:Node :Node - named parameter

answered Sep 12 '11 at 05:00

SMka

3,021
18
14

How to do a LIKE database query in Symfony2

5 Answers5