Doctrine2 has problems with over 1000 rows when choosing

Question

I have this query that returns me all the POIs in a given area:

$query = $em ->createQuery( 'SELECT f FROM MyApplication\MyBundle\Entity\POI p WHERE (p.latitude BETWEEN :southEastLatitude AND :norhtWestLatitude) AND (p.longitude BETWEEN :southEastLongitude AND :norhtWestLongitude) ORDER BY p.name '); $query->setParameter(":norhtWestLatitude", $northWestLat); $query->setParameter(":norhtWestLongitude", $northWestLng); $query->setParameter(":southEastLatitude", $southEastLat); $query->setParameter(":southEastLongitude", $southEastLng); 

If I try to access with a small area (with parameters with slight differences), I successfully get the result. I think I get the result up to 1000 lines ... I'm not quite sure.

If I try to access with a larger area, I get an empty result set ...

Having somehow fired the same request with the parameters of a larger area, I get the correct set of results (~ 1060 rows).

So I'm curious about the limitations of the doctrine (or even Symfony ??? I use the doctrine in my Symfony2 project), are there any? I also tried $query->setMaxResults(999999999); , but it did not help...

Has anyone had the same problem?

Edit: Maybe php memory usage should be high? I added these lines before and after getresult:

 echo "Memory usage before: " . (memory_get_usage() / 1024) . " KB" . PHP_EOL; echo "Memory usage after: " . (memory_get_usage() / 1024) . " KB" . PHP_EOL; 

The output was:

Memory usage after: 5964.0625 KB

Memory usage after: KB 10019.421875

Edit: Strange tests:

1) If I test these parameters:

 WHERE (f.latitude BETWEEN 45.64273082966722 AND 47.29965978937995) AND (f.longitude BETWEEN 4.93262593696295 AND 9.99999999999999) 

I get 923 lines (normal behavior)

2) If I change the parameter 9.999999999999 to 10.000000000 (or some number is greater than 9.9999999), I get an empty result set in my application. The database still returns 923 rows (for 10.000000000).

EDIT: I could solve the problem discussed here: https://groups.google.com/d/msg/doctrine-user/qLSon6m4nM4/y5vLztHcbDgJ

Answer 1

From the Doctrine2 mailing list, I found out that you displayed the properties as a string type:

 /** @ORM\Column(name="latitude", type="string", length=255, nullable=false) */ private $latitude; /** @ORM\Column(name="longitude", type="string", length=255, nullable=false) */ private $longitude; 

This means that your db will have VARCHAR columns for these properties. Therefore, when db launches your request, it will perform string comparisons.

String comparisons are different from (normal) comparison numbers. See the following results:

 $a = 1234567890; $b = 987654321; echo $a == $b ? '0' : ($a < $b ? '-1' : '1'); // output: 1 (means $a is bigger than $b) echo strcmp( $a, $b ); // output: -8 (means $a is smaller than $b) 

Therefore, it is best to display the properties as something that represents the numbers in your db (DECIMAL would be a good choice):

 /** @ORM\Column(name="latitude", type="decimal", nullable=false) */ private $latitude; /** @ORM\Column(name="longitude", type="decimal", nullable=false) */ private $longitude; 

PS: You indicate that you get the correct results when you execute the query yourself. This probably happened because you did:

 ... latitude BETWEEN 45.64273082966722 AND 47.29965978937995 ... 

But Doctrine (because you matched properties as strings):

 ... latitude BETWEEN '45.64273082966722' AND '47.29965978937995' ... 

Check out the quotes here. There is a big difference in how these two statements are handled, as the test case shows;)

Answer 2

This is probably due to memory usage. Check the Apache log for memory errors.

When choosing large amounts of data, it may be useful to use $query->iterate() instead of $query->getResult() , as described in this article .

To check if your query generates the correct SQL, you can register or otherwise display the result of calling $query->getSQL() (after setting the parameters). Download the resulting SQL into the database client of your choice to see if the desired result returns.