Error when using "ALL" operator when execute query

Question

I need to execute following query using phalcon framework: "SELECT id FROM table GROUP BY id HAVING '31' = ALL(array_agg(status))" How can I execute this query using phalcon? When I do following:

 Model::query()
   ->columns(['id'])
   ->groupBy('id')
   ->having('31 = ALL(array_agg(status))')
   ->execute();

I get this error message: Syntax error, unexpected token ALL, near to '(array_agg(status)) ', when parsing: SELECT id FROM [SomeNameSpace\Model] GROUP BY [id] HAVING 31 = ALL(array_agg(status)) (137)

Nikolay Mihaylov Nikolay Mihaylov · Accepted Answer · 2018-09-28T15:26:15

I'm not 100% sure which Postgres functions are supported, but you can try like this:

Model::query()
    ->columns([
        'id',
        'ALL(array_agg(status)) AS statusCounter'
    ])
    ->groupBy('id')
    ->having('31 = statusCounter')
    ->execute();

Notice that I moved the aggregation functions in the select, rather in having clause.

UPDATE: here is an example of very custom query. Most functions used are not supported and it's sometimes cleaner just to write a simple SQL query and bind the desired Model to it:

public static function findNearest($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::findNearest([
    'lat' => 42.4961756,
    'lng' => 27.471543300000008
])

Error when using "ALL" operator when execute query

2 Answers