CODEWITHSUNDEEP
phpmysqldoctrine-orm
Jonathan
Jonathan

Reputation: 3024

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

Upvotes: 1

Views: 35

Answers (1)

baldrs
baldrs

Reputation: 2161

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.

Upvotes: 1

Related Questions