2
$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?

  • All where query or only first where query – Amit Bera Jul 22 at 9:39
0

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

  • without registry is it possible to use $sql = //Your Custom SQL Query; $connection->query($sql) ? – Naiwrita09 Jul 22 at 5:55
  • No you have to include registry – D C Bliss Jul 22 at 6:51
0

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.

  • Actually didnt find any such examples so I was confused if it is possible that way? – Naiwrita09 Jul 22 at 4:44
  • It is possible I implement it my self in some places – ASQ Jul 22 at 4:46
  • if I write only till getselect() what will be the difference in output? – Naiwrita09 Jul 22 at 5:04
  • And its not working I think Its fetching some connection to the main table from the first where clause. but I am confused. – Naiwrita09 Jul 22 at 5:05
  • null, because query starts after getSelect(), you can print the query echo $collection->toString() – ASQ Jul 22 at 5:07
0

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.

  • na only first where query – Naiwrita09 Jul 22 at 9:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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