Add custom sql in getProductCollection, or insert geolocation formula

4
votes

I tried to sort the product list in magento by geolocation data with the help of the following article : https://developers.google.com/maps/articles/phpsqlsearch_v3?hl=hu-HU .

I overrided the Mage_Catalog_Block_Product_List in my module so that I can reach this line of code :

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

Once I have the _productCollection object, I want to add the formula as a "virtual" attribute "distance" so that I can sort the query with it. Here's what I have in mind :

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

I know the API's not made to handle that sort of syntax but I think it could help people get the idea.

Here's the formula I wanna 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 it ?

1
magentogeolocation

1 Answers

1
votes

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 the custom select query, lat and lng need to be enclosed in double brackets (as shown). Make sure your select statement is within parentheses.