0

I'm learning Symfony2 and I don't understand because a DQL query not works but SQL query works correctly.

My DB is this:

mysql> mysql> select * from event;
+----+-------------+------------------+-------------------------+-------------------+----------------------+---------------------+---------+---------------------+---------------------+
| id | promoter_id | title            | location                | lat               | lng                  | date                | program | createdAt           | updatedAt           |
+----+-------------+------------------+-------------------------+-------------------+----------------------+---------------------+---------+---------------------+---------------------+
|  1 |           2 | St Blai VdC 2017 | Vilar de Canes, Espanya | 40.35827889999999 | -0.06620989999998983 | 2017-02-04 00:00:00 | :)      | 2016-11-16 13:52:58 | 2016-11-16 13:52:58 |
|  2 |           1 | Festes Agost VdC | Vilar de Canes, Espanya | 40.35827889999999 | -0.06620989999998983 | 2017-08-04 00:00:00 | xD      | 2016-11-16 13:53:39 | 2016-11-16 13:53:39 |
|  3 |           6 | Festes Agost VdC | Vilar de Canes, Espanya | 40.35827889999999 | -0.06620989999998983 | 2017-08-05 00:00:00 | xxxD    | 2016-11-16 13:54:24 | 2016-11-16 13:54:24 |
|  4 |           1 | qwer             | Castelló, Espanya       | 40.14517720000001 | -0.14949879999994664 | 2016-11-17 15:50:00 | asdfas  | 2016-11-17 15:51:27 | 2016-11-17 15:51:27 |
|  5 |           1 | lkn              | Castelló, Espanya       | 40.14517720000001 | -0.14949879999994664 | 2016-11-17 22:43:00 | asdf    | 2016-11-17 22:43:31 | 2016-11-17 22:43:31 |
|  6 |           1 | lkñk             | Albocàsser, Espanya     | 40.35670100000001 | 0.025070499999969797 | 2016-11-17 22:43:00 | lkjo    | 2016-11-17 22:43:58 | 2016-11-17 22:43:58 |
|  7 |           2 | ertop            | Benassal, Espanya       | 40.3797068        | -0.14195100000006278 | 2016-12-17 22:44:00 | lñkjok  | 2016-11-17 22:44:27 | 2016-11-17 22:44:27 |
+----+-------------+------------------+-------------------------+-------------------+----------------------+---------------------+---------+---------------------+---------------------+
7 rows in set (0,00 sec)

SQL that works correctly:

mysql> select * from event where lat > 40.34 and lat < 40.37 and lng > -0.07 and lng < -0.05 order by date asc;
+----+-------------+------------------+-------------------------+-------------------+----------------------+---------------------+---------+---------------------+---------------------+
| id | promoter_id | title            | location                | lat               | lng                  | date                | program | createdAt           | updatedAt           |
+----+-------------+------------------+-------------------------+-------------------+----------------------+---------------------+---------+---------------------+---------------------+
|  1 |           2 | St Blai VdC 2017 | Vilar de Canes, Espanya | 40.35827889999999 | -0.06620989999998983 | 2017-02-04 00:00:00 | :)      | 2016-11-16 13:52:58 | 2016-11-16 13:52:58 |
|  2 |           1 | Festes Agost VdC | Vilar de Canes, Espanya | 40.35827889999999 | -0.06620989999998983 | 2017-08-04 00:00:00 | xD      | 2016-11-16 13:53:39 | 2016-11-16 13:53:39 |
|  3 |           6 | Festes Agost VdC | Vilar de Canes, Espanya | 40.35827889999999 | -0.06620989999998983 | 2017-08-05 00:00:00 | xxxD    | 2016-11-16 13:54:24 | 2016-11-16 13:54:24 |
+----+-------------+------------------+-------------------------+-------------------+----------------------+---------------------+---------+---------------------+---------------------+
3 rows in set (0,00 sec)

My DQL query in EventRepository that not works:

$query = $em->createQuery(
            "SELECT e
            FROM AppBundle:Event e
            WHERE e.lat >= :latBoundMin AND e.lat <= :latBoundMax AND e.lng >= :lngBoundMin AND e.lng <= :lngBoundMax
            ORDER BY e.date ASC"
        );

            // WHERE (e.lat BETWEEN :latBoundMin AND :latBoundMax) AND (e.lng BETWEEN :lngBoundMin AND :lngBoundMax)

The runnable DQL query detailed in Symfony's profiler:

SELECT e0_.id AS id_0, e0_.title AS title_1, e0_.location AS location_2, e0_.lat AS lat_3, e0_.lng AS lng_4, e0_.date AS date_5, e0_.program AS program_6, e0_.createdAt AS createdAt_7, e0_.updatedAt AS updatedAt_8, e0_.promoter_id AS promoter_id_9 FROM event e0_ WHERE e0_.lat >= 40.3457789 AND e0_.lat <= 40.3707789 AND e0_.lng >= -0.07870989999999 AND e0_.lng <= -0.05370989999999 ORDER BY e0_.date ASC;

This is my indexAction in my EventController:

public function indexAction(Request $request)
    {
        $em = $this->getDoctrine()->getManager();
        $lat = $request->request->get("locationLat");
        $lng = $request->request->get("locationLng");
        $zoom = 0;

            $events = $em->getRepository("AppBundle:Event")->findByBounds($lat, $lng, $zoom);

        return $this->render('event/index.html.twig', array(
            'events' => $events,
        ));
    }

And in my EventRepository I define the method findByBounds:

public function findByBounds($lat, $lng, $zoom)
{
    $em = $this->getEntityManager();
    $bounds = 0.025;

    $latBound1 = $lat + $bounds * 2 ** $zoom / 2;
    $latBound2 = $lat - $bounds * 2 ** $zoom / 2;
    $lngBound1 = $lng + $bounds * 2 ** $zoom / 2;
    $lngBound2 = $lng - $bounds * 2 ** $zoom / 2;

    $latBoundMin = $latBound1 < $latBound2 ? $latBound1 : $latBound2;
    $latBoundMax = $latBound1 > $latBound2 ? $latBound1 : $latBound2;
    $lngBoundMin = $lngBound1 < $lngBound2 ? $lngBound1 : $lngBound2;
    $lngBoundMax = $lngBound1 > $lngBound2 ? $lngBound1 : $lngBound2;

    $query = $em->createQuery(
        "SELECT e
        FROM AppBundle:Event e
        WHERE e.lat >= :latBoundMin AND e.lat <= :latBoundMax AND e.lng >= :lngBoundMin AND e.lng <= :lngBoundMax
        ORDER BY e.date ASC"
    );
    // WHERE (e.lat BETWEEN :latBoundMin AND :latBoundMax) AND (e.lng BETWEEN :lngBoundMin AND :lngBoundMax)

    $query->setParameters(array(
        "latBoundMin" => $latBoundMin,
        "latBoundMax" => $latBoundMax,
        "lngBoundMin" => $lngBoundMin,
        "lngBoundMax" => $lngBoundMax,
    ));

    return $query->getResult();
}

I don't understand why not works. The query returns 0 rows and it must return 3 rows :( Please help.

Vic B
  • 1
  • 3
  • 1
    What doesn't work, can you post the error? I also don't see in your code where you'd setting the parameters – mickadoo Nov 20 '16 at 00:09
  • Hi @mickadoo, thanks for your answer. I modify the post adding the code needed. Thank you for your help. – Vic B Nov 20 '16 at 09:31
  • You still didn't say what's not working :-) Do you get an error or an empty result? If you don't see anything check the logs – mickadoo Nov 20 '16 at 10:29
  • Sorry @mickadoo :S I get an empty result. The runnable query showed in Symfony's profiler is correct I believe and I don't understand what I'm doing bad – Vic B Nov 20 '16 at 10:58
  • Does a simple DQL query without the WHERE part work? – mickadoo Nov 20 '16 at 11:58
  • Yes, works correctly without the WHERE. It shows all rows. – Vic B Nov 20 '16 at 12:02
  • If you look at the differences between the SQL and generated query maybe it's some clue. The generated query has further decimal places. Other than that I don't see much wrong with your query. – mickadoo Nov 20 '16 at 12:04
  • Mmm...with this works correctly: WHERE e.lat >= 40.25 and e.lat <= 40.45 and e.lng >= -0.07 and e.lng <= -0.05 Maybe the problem are the parameters? – Vic B Nov 20 '16 at 12:05
  • That sounds like it. It would be nice to know exactly why it's not working but maybe you can experiment with casting your parameters to a float or using floatval()? – mickadoo Nov 20 '16 at 12:09
  • Me too! I mean if you var dump your parameters just before the query and manually insert them in the SQL it works? – mickadoo Nov 20 '16 at 12:27
  • not works either. If I do: $latBoundMin = 40.25; $latBoundMax = 40.45; $lngBoundMin = -0.07; $lngBoundMax = -0.05; The query not works. – Vic B Nov 20 '16 at 12:33
  • But it does work if you insert those values in the DQL and don't use setParameter? – mickadoo Nov 20 '16 at 12:38
  • yes, works correctly – Vic B Nov 20 '16 at 12:41
  • Hmm, the problem must be in setParameters so, I don't know if it makes any difference but you could try `setParameter(":name", floatval($parameter))` for each parameter. Really I'm just guessing now, sorry – mickadoo Nov 20 '16 at 12:45
  • Still not working @mickadoo. Thanks for all :) – Vic B Nov 20 '16 at 12:48
  • Maybe you could try with BETWEEN and using the query builder? http://stackoverflow.com/a/12431539/1196369 – mickadoo Nov 20 '16 at 17:58
  • Not works. I used BETWEEN before comparision operators. I believe that is a logical problem but I'm not sure. – Vic B Nov 20 '16 at 18:42

0 Answers0