Add custom sql to getProductCollection or insert geolocation formula

Question

I tried to sort the list of products in magento according to geolocation using the following article: https://developers.google.com/maps/articles/phpsqlsearch_v3?hl=hu-HU .

I overridden Mage_Catalog_Block_Product_List in my module to achieve this line of code:

$this->_productCollection = $layer->getProductCollection(); 

As soon as I have the _productCollection object, I want to add the formula as a “virtual” distance attribute so that I can sort the query with it. Here is what I mean:

 $this->_productCollection = $layer->getProductCollection() ->addAttributeToSelect('distance','I insert my custom formula here') ->addAttributeToSort('distance','DESC'); 

I know that the API is not designed to handle such syntax, but I think it can help people understand this idea.

Here is the formula I want to add:

SELECT (3959 * acos (cos (radians (37)) * cos (radians (lat)) * cos (radians (lng) - radians (-122)) + sin (radians (37)) * sin (radians (lat) ))) AS distance

Any idea how to do this?

Answer 1

If lat and lng are attributes of your product, you can use addExpressionAttributeToSelect ():

 $this->_productCollection ->addExpressionAttributeToSelect('distance', '(select ... {{lat}} ... {{lng}} ... )', array('lat'=>'lat', 'lng'=>'lng')); 

In a custom select request, lat and lng must be enclosed in double brackets (as shown). Make sure your select statement is in parentheses.