Yii2 active query compatible with geometrical data types.

ActiveQuery.php

<?php

namespace common\components;

use yii\db\ActiveQuery as YiiActiveQuery;

/**
 * Class providing additional features for yii\db\ActiveQuery. Add support for JSON and spatial data types.
 * 
 * @since 0.0.1
 */
class ActiveQuery extends YiiActiveQuery
{
    protected $_skipPrep = false;
    /**
     * A filter method that enables searching for records based on distance from original $from point.
     * 
     * @since 0.0.1
     * @param string $from The point we're calculating distance from. Latitude and longitude in form of 58.3782,26.7296.
     * @param string $attribute The DB attribute containing location of records we're comparing against.
     * @param integer $radius The maxium radius we're searching in.
     * @param string $unit The unit in which we're calculating the distance. Accepted values are `km` or `miles`. Defaults to `km`.
     * @return $this
     * @link https://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions-object-shapes.html#function_st-distance
     */
    public function nearest($from, $attribute, $radius = 100, $unit='km')
    {
        $lenPerDegree = 111.045;    // km per degree latitude; for miles, use 69.0
        if ($unit=='miles') $lenPerDegree = 69.0;

        $from = explode(',', $from);
        if (! is_array($from)) return $this;

        $lat = trim($from[0]);
        $lng = trim($from[1]);

        /** @var \yii\db\ActiveRecord $modelCls */
        $modelCls = $this->modelClass;

        if ($modelCls::getDb()->driverName === 'mysql') {
            $subQuery = $this->create($this)->from($modelCls::tableName())
                ->select(['*', '_d' => "($lenPerDegree * ST_Distance($attribute, ST_PointFromText(:point)))"])
                ->params([':point' => "POINT($lat $lng)"]);
        } else if ($modelCls::getDb()->driverName === 'pgsql') {
            $subQuery = $this->create($this)->from($modelCls::tableName())
                ->select(['*', '_d' => "($lenPerDegree * ($attribute <-> POINT(:lt,:lg)))"])
                ->params([':lg' => $lng, ':lt' => $lat]);
        }

        $this->from([$subQuery])
            ->andWhere([ '<', '_d', $radius ])
            ->orderBy([
                '_d' => SORT_ASC
            ]);

        $this->limit = null;
        $this->offset = null;
        $this->distinct = null;
        $this->groupBy = null;
        $this->join = null;
        $this->union = null;

        return $this;
    }
    
    /**
     * A filter method that checks whether given DB `$attribute` contains given `$geometry`. The opposite of `within()`.
     * 
     * For example the following query will find all records of Country model where ID is less than 2 and it's `territory` contains a point POINT(1 1):
     * ```php
     * $results = \common\models\Country::find()->where(["<", "id", 2])->contains("territory", "POINT(1 1)")->all();
     * ```
     * 
     * @since 0.0.1
     * @param string $attribute The DB geometry attribute
     * @param string $geometry The geometry that `$attribute` should contain.
     * @return $this
     * @throws \yii\base\NotSupportedException
     * @link https://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions-object-shapes.html#function_st-contains
     */
    public function contains($attribute, $geometry)
    {
        $modelCls = $this->modelClass;
        
        if ($modelCls::getDb()->driverName !== "mysql") {
            throw new \yii\base\NotSupportedException("Current ActiveQuery class is written for MySQL only.");
        }
        $subQuery = $this->create($this)->from($modelCls::tableName())
            ->select(["*"]);
        
        $this->from($modelCls::tableName())
            ->andWhere("st_contains($attribute, GeomFromText(:geom))")
            ->params([":geom" => $geometry]);
        
        $this->limit = null;
        $this->offset = null;
        $this->distinct = null;
        $this->groupBy = null;
        $this->join = null;
        $this->union = null;        
        
        return $this;
    }
    
    /**
     * A filter method that checks if `$attribute` is within the range of `$geometry`. The opposite of `contains()`.
     * 
     * For example the following query will filter results where attribute `coordinates` is within a given POLYGON:
     * ```php
     * $results = \common\models\Country::find()->where(["<", "id", 2])->within("coordinates", "POLYGON((100 100, 100 -100, -100 -100, -100 100, 100 100))")->all();
     * ```
     * 
     * @since 0.0.1
     * @param string $attribute The DB geometry attribute.
     * @param string $geometry The geometry that should contain the `$attribute`.
     * @return $this
     * @throws \yii\base\NotSupportedException
     * @link https://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions-object-shapes.html#function_st-within
     */
    public function within($attribute, $geometry)
    {
        $modelCls = $this->modelClass;
        if ($modelCls::getDb()->driverName !== "mysql") {
            throw new \yii\base\NotSupportedException("Current ActiveQuery class is written for MySQL only.");
        }

        $this->from($modelCls::tableName())
            ->andWhere("st_within($attribute, GeomFromText(:geom))")
            ->params([":geom" => $geometry]);
        
        $this->limit = null;
        $this->offset = null;
        $this->distinct = null;
        $this->groupBy = null;
        $this->join = null;
        $this->union = null;          
        
        return $this;
    }
    
    /**
     * Helper method that enables using JSON data fields on filtering. Currently supports only JSON_CONTAINS!
     * 
     * @since 0.0.1
     * @todo Make it elegant! And add other JSON functions
     * @param array $condition The JSON condition
     * @return $this
     * @throws \yii\base\NotSupportedException
     * @link https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-contains
     */
    public function andFilterJson(array $condition)
    {
        $modelCls = $this->modelClass;
        if ($modelCls::getDb()->driverName !== "mysql") {
            throw new \yii\base\NotSupportedException("Current ActiveQuery class is written for MySQL only.");
        }
        
        $condition = $this->filterCondition($condition);
        if ($condition !== [] && !empty($condition[2])) {
            if (strtolower($condition[0]) !== "json_contains") {
                throw new \yii\base\NotSupportedException("Currently only 'json_contains' is allowed for JSON filtering.");
            }
            $this->from($modelCls::tableName())
                //->andWhere($condition[0] . "(" . $condition[1] . ", '" . json_encode($condition[2]) . "')");
                ->andWhere(sprintf("%s(%s, '%s')", $condition[0], $condition[1], json_encode($condition[2])));
        }
        return $this;
    }   

    /**
     * {@inheritDoc}
     */
    protected function queryScalar($selectExpression, $db) 
    {
        $this->_skipPrep = true;
        $r = parent::queryScalar($selectExpression, $db);
        $this->_skipPrep = false;
        return $r;
    }

    /**
     * Prepare building SQL for JSON type of columns
     * 
     * @since 0.0.1
     * @param \yii\db\QueryBuilder $builder
     * @return $this
     */
    public function prepare($builder)
    {        
        /** @var ActiveRecord $modelClass */
        $modelClass = $this->modelClass;
        if ($modelClass::getDb()->driverName === 'pgsql') return parent::prepare($builder);

        if (! $this->_skipPrep) {   // skip in case of queryScalar; it's not needed, and we get an SQL error (duplicate column names)
            if (empty($this->select))   {
                $this->select('*');
                $this->allColumns();
            }
            else   {
                $schema = $modelClass::getTableSchema();
                foreach ($this->select as $field) {
                    if ($field == '*')  {
                        $this->allColumns();
                    }
                    else {
                        $column = $schema->getColumn($field);
                        if (ActiveRecord::isPoint($column) || ActiveRecord::isPolygon($column)) {
                            $this->addSelect(["ST_AsText($field) AS $field"]);
                        }
                    }
                }
            }
        }
        return parent::prepare($builder);
    }

    /**
     * Prepare SQL to select JSON values as text.
     * 
     * @since 0.0.1
     */
    protected function allColumns()
    {
        /** @var ActiveRecord $modelClass */
        $modelClass = $this->modelClass;
        $schema = $modelClass::getTableSchema();
        foreach ($schema->columns as $column)   {
            if (ActiveRecord::isPoint($column) || ActiveRecord::isPolygon($column)) {
                $field = $column->name;
                $this->addSelect(["ST_AsText($field) AS $field"]);
            }
        }
    }
}