i want to write custom sql query to select low and high price in magento2?

Question

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)): ?>
                selected="selected"
            <?php endif; ?>
            >
            <?php //echo $block->escapeHtml(__($_order == "Position" ? 'Relevance': $_order)) ?>
            <?php echo $block->escapeHtml(__($_order)) ?>
        </option>
    <?php endif; ?>
    <?php endforeach; ?>
    <?php 
        $url = $_SERVER['REQUEST_URI'];
        $path = strstr($url, '?'); 
    ?>
    <option 
        <?php if($path == '?product_list_order=price&product_list_dir=asc'): ?>
            selected="selected" 
        <?php endif; ?> 
        class="low-high" >
        Price - Low to High
    </option>
    <option 
        <?php if($path == '?product_list_order=price&product_list_dir=desc'): ?>
            selected="selected" 
        <?php endif; ?> 
        class="high-low" >
        Price - High to Low
    </option>
</select>

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.

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'];