Jonathan Jonathan - 5 months ago 14
SQL Question

Doctrine2 NEW syntax

http://sqlfiddle.com/#!9/b2b34e/7

I'm trying to figure out how write the following using Doctrine2's NEW syntax:

SELECT
id, username, (
6371 * acos (
cos ( radians(53.3646732) )
* cos( radians( latitude ) )
* cos( radians( longitude ) - radians(-6.235036) )
+ sin ( radians(53.3646732) )
* sin( radians( latitude ) )
)
) AS distance
FROM users
HAVING distance < 50
ORDER BY distance
LIMIT 0 , 20;


So far I have tried this:

$query = $em->createQuery('
SELECT NEW GU\UserBundle\Model\NearbyUser(u.id,
6371 * acos (
cos ( radians(53.3646732) )
* cos( radians( u.latitude ) )
* cos( radians( u.longitude ) - radians(-6.235036) )
+ sin ( radians(53.3646732) )
* sin( radians( u.latitude ) )
)
)
FROM GU\UserBundle\Entity\User u');


How do I also set this part to a variable called
distance
without throwing an error so I can use it
HAVING distance < 50
:

6371 * acos (
cos ( radians(53.3646732) )
* cos( radians( latitude ) )
* cos( radians( longitude ) - radians(-6.235036) )
+ sin ( radians(53.3646732) )
* sin( radians( latitude ) )
)
) AS distance


UPDATE:
I don't think im doing this part right:

$query = $em->createQuery('
SELECT NEW GU\UserBundle\Model\NearbyUser(u.id,
(6371 * acos (
cos ( radians(53.3646732) )
* cos( radians( u.latitude ) )
* cos( radians( u.longitude ) - radians(-6.235036) )
+ sin ( radians(53.3646732) )
* sin( radians( u.latitude ) )
)) as distance
)
FROM GU\UserBundle\Entity\User u');


gives the error:


[Syntax Error] line 0, col 367: Error: Expected
Doctrine\ORM\Query\Lexer::T_FROM, got ')'

Answer

As far as I know you cannot do it with SELECT NEW syntax, and what you're trying to do is invalid syntax anyway, so you've got two options:

  1. Drop SELECT NEW and load u.id, your formula here AS distance as array and then create entities as needed(and attach them to entity manager). This will yield less rows and will be more efficient as you will need less entities(or perhaps won't need any, if you don't intend to persist them for example)
  2. Lazy-load all entities and filter them by distance, destroying all entities which are not passing the check to free memory. That will consume far more resources and not recommended way.