How can I run this query in Phalcon?

Spatial functions are not supported by PHQL, so you have 2 options:

1. Create your own Dialect class and add those functions: https://docs.phalcon.io/en/3.3/api/Phalcon_Db_Dialect_Mysql
2. Use raw queries: https://docs.phalcon.io/uk/3.2/db-phql#raw-sql

I would go with option 2 perhaps :)

Hi,

I think option 2 would be better, but how can I bound values @lat and @lng, as the link only shows how to bind where conditions?

Thanks

I think @lat & @lng are just variables - this looks like a stored procedure maybe?

To make it work, look into binding and prepared queries with PDO.

Lucky for you I dug out a database from old project with geometry calculations :)

Tested and it is working as intended, enjoy:

// Sample model method
public static function findByRawSql($params = null)
{
    // A raw SQL statement
    $sql = '
        SELECT *, 111.045 * DEGREES(ACOS(COS(RADIANS(:lat))
         * COS(RADIANS(X(coords)))
         * COS(RADIANS(Y(coords)) - RADIANS(:lng))
         + SIN(RADIANS(:lat))
         * SIN(RADIANS(X(coords)))))
         AS distance_in_km
        FROM object_locations
        ORDER BY distance_in_km ASC
        LIMIT 0,5;    
    ';

    // Base model
    $model = new ObjectLocations();

    // Execute the query
    return new \Phalcon\Mvc\Model\Resultset\Simple(
        null,
        $model,
        $model->getReadConnection()->query($sql, $params)
    );
}

// How to use:
\Models\ObjectLocations::findByRawSql([
    'lat' => 42.4961756,
    'lng' => 27.471543300000008
])

P.S. note that I replaced your latitude & longitude with X(coords) and Y(coords) since I was storing coordinates in a single POINT field, make sure to replace with your fields :)

Perfect, up and running, thanks :)

Just an update, I have a strange issue. The query seems to be retrieveing the right reults, but is:

1. Not returning the value distance_in_km
2. I changed the wildcard to only retrieve the desired fields which works fine, but it is aldo returning the fields not requested but with a value of null
3. I tried to add select name as shopname but the query returns the value as name

Thanks

How come? Add some code please. Just did some random tests and its working as intended:

public static function findByRawSql($params = null)
{
    // A raw SQL statement
    $sql = '
        SELECT object_id AS objId, 111.045 * DEGREES(ACOS(COS(RADIANS(:lat))
         * COS(RADIANS(X(coords)))
         * COS(RADIANS(Y(coords)) - RADIANS(:lng))
         + SIN(RADIANS(:lat))
         * SIN(RADIANS(X(coords)))))
         AS distance_in_km
        FROM object_locations
        ORDER BY distance_in_km ASC
        LIMIT 0,5;    
    ';

    // Base model
    $model = new ObjectLocations();

    // Execute the query
    return new \Phalcon\Mvc\Model\Resultset\Simple(
        null,
        $model,
        $model->getReadConnection()->query($sql, $params)
    );
}

Results:

Array
(
    [0] => Array
        (
            [objId] => 29
            [distance_in_km] => 0
        )

    [1] => Array
        (
            [objId] => 2
            [distance_in_km] => 0.061588850090002624
        )

    [2] => Array
        (
            [objId] => 2
            [distance_in_km] => 1.2645346926790209
        )

    [3] => Array
        (
            [objId] => 33
            [distance_in_km] => 2.339252413330337
        )

    [4] => Array
        (
            [objId] => 29
            [distance_in_km] => 2.461791090198477
        )

)

Hi,

Copied and pasted your query and made changes as required and worked fine. Not sure what caused it, when I get a chance will look through my backups and compare. In the meantime everything working well. Thanks