How to use addSelectExpr ?

4 posts by 2 authors in: Forums > CMS Builder
Last Post: December 23, 2009   (RSS)

By Djulia - December 21, 2009 - edited: December 21, 2009

Hi,

I would like to understand the use of the new option addSelectExpr.

For example, if I use this code to obtain the recordings in a radius :

SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM members HAVING distance<='10' ORDER BY distance ASC

...It is possible to simplify the use with addSelectExpr and cmsB like this :

list($geoRecords, $geoMetaData) = getRecords(array(
tableName' => 'geo',
'limit' => '5',
'orderBy' => 'distance ASC',
'addSelectExpr' => ' ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance HAVING distance<='10' ',
));

Thanks,
Djulia

http://www.zcentric.com/blog/2007/03/calculate_distance_in_mysql_wi.html (Thanks Dave !)

Re: [Dave] How to use addSelectExpr ?

By Djulia - December 22, 2009 - edited: December 22, 2009

Hi Dave,

I use a similar request SQL on a real estate site and cmsB. But, my approach limits the possibilities (geo____.txt).

Also, it would be perfect if I could use my request SQL with a page of the list type and the options available (Show perPage [font "Times New Roman"]prev & next, Show file uploads, search…).

Does [font "Times New Roman"]addSelectExpr give this possibility ?

Thank you for your answer.

Djulia
Attachments:

geo____.txt 1K

Re: [Djulia] How to use addSelectExpr ?

By Dave - December 23, 2009

Hi Djulia,

Yes I think it will work for you, but I'll need to add a "having" option since you are using that to. If you can email me CMS and FTP login details and an url to a working test page I can show you how to do it and add "having".

We were planning on adding a "having" option but wanted to wait until we had a real-world example to test it on.

I should be able to get this done in the next week (slower because of the holiday).

Remember to email (don't post login details to the forum).

Hope that helps! :)
Dave Edis - Senior Developer
interactivetools.com