Magento 2 delete search term through sql query

Question

I want to delete search terms with 0 search results from magento 2 database by writing sql query in file. How can I do this?

• purna gattu: We do have an option delete the search terms from the admin , On the Admin sidebar, tap Marketing. Then under SEO & Search, choose Search Terms. here we have option to filter results in the grid and delete it.
• chanchal: I want to create a file in magento which I will run through cron job everyday to delete the search terms automatically. I am asking for solution in code file.
• purna gattu: Delete the records from the 'search_query' whose 'num_results = 0'.

Answer 1

The query should be like,

DELETE FROM `search_query` WHERE `num_results` = 0;

Or you can use following PHP code.

protected $queryCollectionFactrory;
public function __construct(
 \Magento\Search\Model\ResourceModel\Query\CollectionFactory $queryCollectionFactrory
)
{
 $this->queryCollectionFactrory = $queryCollectionFactrory;
}
public function removeZeroResults()
{
 $collection = $this->queryCollectionFactrory->create();
 $collection->addFieldToFilter('num_results', 0);
 foreach($collection as $row)
 {
 $row->delete();
 }
}

• chanchal: where to place the above php code file ? I mean to say the path . Do I need to create a custom module for this ?
• Yash Shah: You can create a custom module or you can place this code on whatever event you want to delete records with 0 search result.

Answer 2

Select * FROM search_query WHERE 'query_text' LIKE '% textYouWant%'

once you see the values then you can do this : Delete FROM search_query WHERE 'query_text' LIKE '% textYouWant%'