CODEWITHSUNDEEP
phpmysqlzend-framework
directory
directory

Reputation: 3167

ZF2 Query remove table name in column function

I am trying to translate a query in my example to a ZF2 query. I almost got it work but the column (AS) function mess it up by adding the table name in front of my calculation.

Working query example;

SELECT id, city, ( 6371 * acos( cos( radians(51.5009745484064) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(3.6766170173753) ) + sin( radians(51.5009745484064) ) * sin( radians( lat ) ) ) ) AS distance 
FROM zipcodes 
HAVING distance < 100 
ORDER BY distance desc 
LIMIT 0 , 20;

ZF2 query output;

SELECT "zipcodes"."id" AS "id", "zipcodes"."city" AS "city", "zipcodes"."(6371 * acos(cos(radians(51.5009745484064)) * cos(radians(lat)) * cos(radians(lng)-radians(3.6766170173753)) + sin(radians(51.5009745484064)) * sin(radians(lat))))" AS "distance" 
FROM "zipcodes" 
HAVING distance < '25' 
ORDER BY "distance" 
ASC LIMIT '10'

ZF2 query

# Expression
$having = new \Zend\Db\Sql\Having();
$having->expression('distance < ?', $radius);

# Select
$select = new Select();
$select->from($this->table)
        ->columns(array('id', 'city', 'distance' => '(6371 * acos(cos(radians(51.5009745484064)) * cos(radians(lat)) * cos(radians(lng)-radians(3.6766170173753)) + sin(radians(51.5009745484064)) * sin(radians(lat))))'))
        ->having($having)
        ->order($order)
        ->limit($limit);

$rowset = $this->selectWith($select);
return $rowset->current();

Upvotes: 2

Views: 167

Answers (1)

Stanimir Dimitrov
Stanimir Dimitrov

Reputation: 1890

Always read the source code. It will save you hours.

/**
 * Specify columns from which to select
 *
 * Possible valid states:
 *
 *   array(*)
 *
 *   array(value, ...)
 *     value can be strings or Expression objects
 *
 *   array(string => value, ...)
 *     key string will be use as alias,
 *     value can be string or Expression objects
 *
 * @param  array $columns
 * @param  bool  $prefixColumnsWithTable
 * @return Select
 */
public function columns(array $columns, $prefixColumnsWithTable = true)
{
    $this->columns = $columns;
    $this->prefixColumnsWithTable = (bool) $prefixColumnsWithTable;
    return $this;
}

From there, just do this:

$select->from($this->table)
        ->columns(array('id', 'city', 'distance' => '(6371 * acos(cos(radians(51.5009745484064)) * cos(radians(lat)) * cos(radians(lng)-radians(3.6766170173753)) + sin(radians(51.5009745484064)) * sin(radians(lat))))'), false)
        ->having($having)
        ->order($order)
        ->limit($limit);

Upvotes: 1

Related Questions