CODEWITHSUNDEEP
phpdatesymfonydoctrine-orm
qerigan
qerigan

Reputation: 51

Fetch date similar to string from database in doctrine

I need to fetch 5 distinct Date type values from database based on a string. When I run SQL query at phpmyadmin the results are correct:

SELECT DISTINCT `date` FROM `collection` WHERE `date` LIKE "%2015-%" ORDER BY `collection`.`date`  DESC LIMIT 0,5

Results:

But when I build the query with Doctrine it basically returns latest 5 dates. It looks like the "LIKE" statement was ignored. Here is the POST controller:

/**
 * @Route("/collection/InputHint", name="collectionInputHint")
 * @Method("POST")
 */
public function collectionInputHint(Request $request)
{
    $string = $request->get('value');
    $entity = $request->get('entity');
    $entityColumn = $request->get('entity-column');
    $entityType = $request->get('entity-type');
    $result = array();

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

    $objects = $em
    ->getRepository('AppBundle:'.$entity)
    ->createQueryBuilder($entity)
    ->select($entity.'.'.$entityColumn)
    ->distinct($entity.'.'.$entityColumn)
    ->where($entity.'.'.$entityColumn.' LIKE :string')
    ->setParameter('string', '%'.$string.'%')
    ->orderBy($entity.'.'.$entityColumn, 'DESC')
    ->setMaxResults(5)
    ->getQuery()
    ->getResult();

    foreach ($objects as $object) {
        $value = ($entityType == 'date') ? $object[$entityColumn]->format("Y-m-d") : $object[$entityColumn];
        array_push($result, (string)$value);
    }

    return new Response(json_encode($result));
}

and the doctrine results are:

Notice first 2 results are less similar to the $string than the rest of results. Also if I change order to ASC there are 5 dates from 2013 so the order is not a problem here. Any ideas?

Upvotes: 0

Views: 546

Answers (2)

qerigan
qerigan

Reputation: 51

Ok, I found the problem. Turned out that the $string was actually empty so the query looked like: ...WHERE 'date' LIKE '%%'...

In JS I was trying to fetch the string from wrong/non-existing object.

After JS fix the results were finally correct so the controller code works fine.

Upvotes: 0

Alvin Bunk
Alvin Bunk

Reputation: 7764

I think this one line is the problem, it should have been obvious when I first looked:

->setParameter('string', '"%'.$string.'%"')

Change that, and I'm fairly sure it will work!

Upvotes: 1

Related Questions