2

I have thousands of property in my store. I'm trying to write automated related properties. Each property has latitude and longitude. When customer click anyone property, we need to suggest five top most near by properties as related properties we have.

I found a way to get nearest product by using below mysql query

    SELECT latitude, longitude, SQRT(POW(69.1 * (latitude - [startlat]), 2) +
POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM TableName HAVING distance < 25 ORDER BY distance;

But it hard to convert as search criteria or apply filter to product collectionFactory

Testing controller

<?php

namespace Bilal\Test\Controller\Index;

class Index extends \Magento\Framework\App\Action\Action
{

    protected $resultPageFactory;
    protected $jsonHelper;

    public function __construct(
        \Magento\Framework\App\Action\Context $context,
        \Magento\Framework\View\Result\PageFactory $resultPageFactory,
        \Magento\Catalog\Model\ProductRepository $productRepository,
        \Magento\Framework\Api\SearchCriteriaInterface $criteria,
        \Magento\Framework\Api\Search\FilterGroup $filterGroup,
        \Magento\Framework\Api\FilterBuilder $filterBuilder,
        \Magento\Catalog\Model\Product\Attribute\Source\Status $productStatus,
        \Magento\Catalog\Model\Product\Visibility $productVisibility


    ) {
        $this->resultPageFactory = $resultPageFactory;
        $this->productRepository = $productRepository;
        $this->searchCriteria = $criteria;
        $this->filterGroup = $filterGroup;
        $this->filterBuilder = $filterBuilder;
        $this->productStatus = $productStatus;
        $this->productVisibility = $productVisibility;
        parent::__construct($context);
    }


    public function execute()
    {


        //Main Product Collection
        $target=$this->productRepository->getById(501);
        $lat =(float) $target->getCustomAttribute('c_lat')->getValue();
        $long =(float)$target->getCustomAttribute('c_long')->getValue();

        // Suggestde Near By Products Using Above Lat Long
        // Below one is return full product collection but I need to apply the above MYSQL query logic here
        $this->filterGroup->setFilters([
            $this->filterBuilder
                ->setField('status')
                ->setConditionType('in')
                ->setValue($this->productStatus->getVisibleStatusIds())
                ->create(),
            $this->filterBuilder
                ->setField('visibility')
                ->setConditionType('in')
                ->setValue($this->productVisibility->getVisibleInSiteIds())
                ->create(),
        ]);

        $this->searchCriteria->setFilterGroups([$this->filterGroup]);
        $products = $this->productRepository->getList($this->searchCriteria);
        $productItems = $products->getItems();

        Return $productItems;

    }


}
1

1 Answer 1

2

In Magento 2.2 (develop branch on github) you can use Magento\Framework\Api\SearchCriteria\CollectionProcessor\FilterProcessor\CustomFilterInterface for applying custom filter using product collection.

In your case, i think, that you need to create your custom service, which will be incapsulate working with product collection and with low-level Magento\Framework\DB\Select object for applying distance condition.

Example:

/**
 * Class ProductLocation.
 */
class ProductLocation extends ProductLocationInterface
{
    //constructor

    /**
     * Get nearby products.
     *
     * @param PointInterface $from
     * @param float $distance
     * @return array
     * @throws \Magento\Framework\Exception\LocalizedException
     */
    public function getNearbyProducts(PointInterface $from, $distance)
    {
        /** @var \Magento\Catalog\Model\ResourceModel\Product\Collection $productCollection */
        $productCollection = $this->productCollectionFactory->create();

        $productCollection->addExpressionAttributeToSelect(
            'distance',
            (string)$this->getDistanceExpression($from->getLatitude(), $from->getLongitude()),
            ['c_lat', 'c_lon']
        );

        $productCollection->getSelect()->having(
            $productCollection->getConnection()->prepareSqlCondition(
                'distance',
                ['lt' => $distance]
            )
        );

        $productCollection->setOrder('distance');

        return $productCollection->getLoadedIds();
    }

    /**
     * Get distance expression.
     *
     * @param float $latitude
     * @param float $longitude
     * @return \Zend_Db_Expr
     */
    private function getDistanceExpression($latitude, $longitude)
    {
        return new \Zend_Db_Expr("
            SQRT(
                POW(69.1 * ({{c_lat}} - {$latitude}), 2) +
                POW(69.1 * ({$longitude} - {{c_lon}}) * COS({{c_lat}} / 57.3), 2)
            )
        ");
    }
}

Note, that this is example, you need to take care about SQL-injection protections and refactor this code for prevent hardcoded variables.

1
  • ( ! ) Fatal error: Uncaught Error: Call to a member function getBackend() on boolean in vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php on line 565 ( ! ) Error: Call to a member function getBackend() on boolean in vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php on line 565 Call Stack Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor->addExpressionAttributeToSelect( ) .../autorelated.php:20 3 0.6565 Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor->addExpressionAttributeToSelect( ) .../Interceptor.php:934 Feb 15, 2018 at 10:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.