How to write a Conditional Selection SQL QUERY in Magento2?

Question

$collection->getSelect()->where($getMainTableAlias.'.entity_id IN (SELECT catText.entity_id FROM '.$catalogCategoryEntityTextTable.' as catText where catText.attribute_id="'.$attributeId.'" AND  FIND_IN_SET("'.$groupId.'", catText.value) GROUP BY   catText.entity_id)');

I have this query. I have to remove the first where clause so as to avoid any filters but the rest will remain same. How can I perform this task?

Answer 1

Replace it with this and add your table name from which you want to get the records.

$collection->getSelect()->SELECT * FROM 'YOUR TABLE NAME';

hope it works.

Answer 2

Try This Code

namespace Vendor\Module\Observer;

use Magento\Framework\Event\Observer; use Magento\Framework\Event\ObserverInterface;

class TEST implements ObserverInterface {

public function __construct(
    \Magento\Framework\Registry $registry,
    \Magento\Framework\App\ResourceConnection $resourceConnection
) {
    $this->registry = $registry;
    $this->resourceConnection = $resourceConnection;
}
public function execute(\Magento\Framework\Event\Observer $observer)
{
        $this->registry->register('isSecureArea', true);
        $orderIncId->delete();
        $connection = $this->resourceConnection->getConnection();
        $tblname= //Table_Name;
        $tableName = $this->resourceConnection->getTableName($tblname);
        $sql = //Your Custom SQL Query;
        $connection->query($sql);
        $this->registry->unregister('isSecureArea');
}

}

May It would help

Answer 3

I don't clearly understand what you have meant by remove the first where clause so as to avoid any filters.

Then you can use the below code:

    $collection->getSelect()
            ->where($getMainTableAlias.'.entity_id IN (SELECT catText.entity_id FROM '.$catalogCategoryEntityTextTable.' '
                    . 'as catText where catText.attribute_id="'.$attributeId.'" AND '
                    . ' FIND_IN_SET("'.$groupId.'", catText.value) GROUP BY   catText.entity_id)');
    // fine the Where condition of collection
    $wherePart = $collection->getSelect()>getPart(\Magento\Framework\DB\Select::WHERE);

    // remove all where condition from Collection

    $collection->getSelect()->reset(\Magento\Framework\DB\Select::WHERE);

    // Remove first condition from $wherePart variable

    if(!empty($wherePart)) {
        // remove first where
        unset($wherePart[0]);
    }
    // Apply Rest of all where condition on collection Again
    if(!empty($wherePart)) {
       $collection->getSelect()>setPart(\Magento\Framework\DB\Select::WHERE,$wherePart);
   }

Using getSelect()->reset(\Magento\Framework\DB\Select::WHERE) you can remove all where condition from the query.