Jonathan - 1 year ago 55

SQL Question

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`

`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

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 Source

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:

- 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) - 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.