Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

doing a find vs dynamic select.

If i have a select statement with select (column1 + column2) as sumofparts from table1 How would I do a Table1::find("sumofparts = '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!



81.8k
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);

Thank you kindly for the quick response!

Cheers!