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

doing a find vs dynamic select.

If i have a select statement with select (column_1 + column_2) as sum_of_parts from table_1 How would I do a Table_1::find("sum_of_parts = '6'"); scenario when the column is not a column in the table it is mapped to?

Basically I am trying to do this... 

SELECT 
  name,
   ( 3959 * acos( cos( radians(42.290763) ) * cos( radians( locations.lat ) ) 
   * cos( radians(locations.lng) - radians(-71.35368)) + sin(radians(42.290763)) 
   * sin( radians(locations.lat)))) AS distance 
FROM locations 
WHERE active = 1 
HAVING distance < 10 
ORDER BY distance;

Thanks!



98.9k
Accepted
answer

Use the query builder:

$rows = $this->modelsManager
            ->createBuilder()
            ->columns([
                'name',
                'distance' => '(3959 * acos(cos(radians(42.290763)) * cos( radians(lat) ) * 
                                                     cos(radians(lng) - radians(-71.35368)) + sin(radians(42.290763)) * sin(radians(locations.lat))))'
            ])
            ->from('Locations')             
            ->where('active = 1')
            ->having('distance < 1')
            ->orderBy('distance');

Or execute the sentence as a PHQL:

$phql = "SELECT 
name,
( 3959 * acos( cos( radians(42.290763) ) * cos( radians( locations.lat ) ) 
* cos( radians(locations.lng) - radians(-71.35368)) + sin(radians(42.290763)) 
* sin( radians(locations.lat)))) AS distance 
FROM locations 
WHERE active = 1 
HAVING distance < 10 
ORDER BY distance";

$rows = $this->modelsManager->executeQuery($phql);


22.7k

Thank you kindly for the quick response!

Cheers!