Fetch date similar to string from database in doctrine

Question

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:

2015-12-31
2015-12-30
2015-12-29
2015-11-30
2015-11-28

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:

2016-01-30
2016-01-29
2015-12-31
2015-12-30
2015-12-28

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?

Have you used the debug environment by appending app_dev.php to the URL and then click on the queries link on the bottom, then look at the database queries to see what was actually run? — Alvin Bunk
– Alvin Bunk, Commented Mar 8, 2017 at 20:43
@AlvinBunk Yes, the doctrine queries log shows: SELECT DISTINCT f0_.date AS date_0 FROM collection f0_ WHERE f0_.date LIKE ? ORDER BY f0_.date DESC LIMIT 5 OFFSET 0 — qerigan
– qerigan, Commented Mar 8, 2017 at 20:48

Alvin Bunk · Accepted Answer · 2017-03-08 20:57:48Z

1

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!

answered Mar 8, 2017 at 20:57

Alvin Bunk

7,8123 gold badges31 silver badges48 bronze badges

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

1 Comment

Alvin Bunk Over a year ago

Don't you I think I deserve at least 1 upvote? Obviously I helped you find the problem!

qerigan · Accepted Answer · 2017-03-08 21:03:18Z

0

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.

answered Mar 8, 2017 at 21:03

qerigan

517 bronze badges

Collectives™ on Stack Overflow

Fetch date similar to string from database in doctrine

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related