I want to write a custom sql query to find final price,min-price,max_price,special_price but i dont know in which file i suppose to write this code and execute?

can anyone tell me where we can write our custom sql query code like suppose in my case to filter price based on final price,min-price,max-price and to return result on category list page in magento 2.

like if i selected product price by low or high,it should display result accordingly. this is my code:

<select id="sorter" data-role="sorter" class="sorter-options selectpicker">
    <?php foreach ($block->getAvailableOrders() as $_key => $_order): ?>
        <?php if($_order != 'Price'): ?>
        <option value="<?php /* @escapeNotVerified */ echo $_key; ?>"
            <?php if ($block->isOrderCurrent($_key)): ?>
            <?php endif; ?>
            <?php //echo $block->escapeHtml(__($_order == "Position" ? 'Relevance': $_order)) ?>
            <?php echo $block->escapeHtml(__($_order)) ?>
    <?php endif; ?>
    <?php endforeach; ?>
        $url = $_SERVER['REQUEST_URI'];
        $path = strstr($url, '?'); 
        <?php if($path == '?product_list_order=price&product_list_dir=asc'): ?>
        <?php endif; ?> 
        class="low-high" >
        Price - Low to High
        <?php if($path == '?product_list_order=price&product_list_dir=desc'): ?>
        <?php endif; ?> 
        class="high-low" >
        Price - High to Low

but it is not filtering the result in correct manner.iam unable to get price from low-high or vice versa when i select filters.

could any one tell me that do we need to write custom sql code in a controller or block. and in which exact file i need tow write the code to get result when i sort product based on price.i also need a special price query where the result can be shown based on final_price and it should include special price if that product is having it.

1 Answer 1


try to make an observer on your action.

Here are all the actions in magento 2 https://cyrillschumacher.com/magento2-list-of-all-dispatched-events/

after this you can use this code to make a query by modify for your need:

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); 
            $resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
            $connection = $resource->getConnection();
            $tableName = $resource->getTableName('eav_attribute_option_value'); 
            $sql = "Select `value` FROM " . $tableName . " where `option_id` =".$health_insurance_id. "";
            $healthAtt = $connection->fetchAll($sql);
             $health_insurance = $healthAtt[0]['value'];
  • how to do it sir can u please explain in brief .i mean how to make action for observer and in which file.iam new to magento Commented Mar 9, 2017 at 17:27
  • i use make observer in inheriting which class is it Magento_catalog/Observer or Magento_Bundle which class to inherit sir Commented Mar 11, 2017 at 5:34
  • any reply please sir? Commented Mar 13, 2017 at 5:39
  • any clue regarding this query sir Commented Mar 14, 2017 at 4:42
  • can you be more specific about what you need? Commented Mar 14, 2017 at 7:55

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.