We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Joins in PHQL

I try to execute this: 

        SELECT user.id, user.name, user_profile.home_location_id, user_profile.current_location_id,
            locations.id, (
              6371 * acos (
              cos ( radians(:lat:) )
              * cos( radians( locations.latitude ) )
              * cos( radians( locations.longitude ) - radians(:lon:) )
              + sin ( radians(:lat:) )
              * sin( radians( locations.latitude ) )
            )
        ) AS distance
        FROM
            Phalcon\UserPlugin\Models\User\UserProfile user_profile
        JOIN ( Phalcon\UserPlugin\Models\User\User user, Phalcon\UserPlugin\Models\Location\Locations locations )
        ON (user_profile.home_location_id != '' and locations.id = user_profile.home_location_id and user.id = user_profile.user_id)
        HAVING distance < 25
        ORDER BY distance
        LIMIT 0 , 20

And i get this error:

Phalcon\Mvc\Model\Exception: Syntax error, unexpected token (, near to 'Phalcon\UserPlugin\Models\User\User user

Any help, please ? @Phalcon ?

1


98.9k

This is happening because of the extra parentheses in the join condition: 

JOIN ( Phalcon\UserPlugin\Models\User\User user, Phalcon\UserPlugin\Models\Location\Locations locations )

It must be:

JOIN Phalcon\UserPlugin\Models\User\User user, JOIN Phalcon\UserPlugin\Models\Location\Locations locations


51.2k

It is not about parentheses. Actually using a plain sql would crash without parentheses . Anyway, i tried again now, but i got the comma error: 

Phalcon\Mvc\Model\Exception: Syntax error, unexpected token COMMA, near to ' Phalcon\UserPlugin\Models\Location\Locations locations ON (user_profile.home_location_id != '' and locations.id = user_profile.home_location_id and user.id = user_profile.user_id) HAVING distance < 25 ORDER BY distance LIMIT 0 , 20 ', when parsing: SELECT user.id, user.name, user_profile.home_location_id, user_profile.current_location_id, locations.id, ( 6371 * acos ( cos ( radians(:lat:) ) * cos( radians( locations.latitude ) ) * cos( radians( locations.longitude ) - radians(:lon:) ) + sin ( radians(:lat:) ) * sin( radians( locations.latitude ) ) ) ) AS distance FROM Phalcon\UserPlugin\Models\User\UserProfile user_profile JOIN Phalcon\UserPlugin\Models\User\User user, Phalcon\UserPlugin\Models\Location\Locations locations ON (user_profile.home_location_id != '' and locations.id = user_profile.home_location_id and user.id = user_profile.user_id) HAVING distance < 25 ORDER BY distance LIMIT 0 , 20 (858)


15.8k

Did you found out the answear? I will need to implement a similar code.



51.2k
Accepted
answer

I use raw sql. Something like:

        $sql = "
        SELECT
            post.user_id as user_id,
            post.id as post_id,
            post.created_at,
            loc.id as location_id,
            distance FROM (
                SELECT id, (
                          6371 * acos (
                          cos ( radians( $lat ) )
                          * cos ( radians( latitude ) )
                          * cos ( radians( longitude ) - radians( $lon ) )
                          + sin ( radians($lat) )
                          * sin ( radians( latitude ) )
                        ) ) AS distance FROM locations HAVING distance < 10 LIMIT 0 , 50
                    ) loc
            JOIN (post, post_hashtags)
            ON (
               post.location_id = loc.id
                   AND
               post.user_id != $user_id 
                   AND
               post_hashtags.post_id = post.id
            )
            GROUP BY (post.user_id)
            ORDER BY distance ASC, post.id DESC
            LIMIT 0 , 50
        ";
        $model = new \MyModel();
        $result  = $model->getReadConnection()->query($sql)


15.8k

i've done it like this. But it doesn't return anything. Do you have any ideea why? I'm trying to retrive locations nearby + pictures from those locations Without the join it works fine.

SELECT Location., Image., (6371 acos(cos(radians(:latitude:)) cos(radians(latitude))

  • cos(radians(longitude) - radians(:longitude:)) + sin(radians(:latitude:))
  • sin(radians(latitude)))) AS distance FROM Location INNER JOIN Image on Location.id = Image.location_id HAVING distance < 20 ORDER BY distance LIMIT 0, 20


15.8k

It works, sorry. It doesn't work fi the location has no images.



51.2k

It's normal. You have the condition Location.id = Image.location_id . Try an outer join maybe ?



51.2k


15.8k

Thank you for the link. Outer join seems to not be recognised by PHQL. Other problem si that in my query it only shows me one image for each location. But I have locations that have many images