1

I'm having trouble writing a query for grouping and concatenating query IDs on Magento's catalogsearch_query table.

Summary: Can I "CONCAT" results of a GROUP_CONCAT?

Here's what I have:

SELECT 
  DISTINCT query_text, 
  store_id, 
  GROUP_CONCAT(query_id ORDER BY query_id) AS queries
FROM catalogsearch_query 
GROUP BY query_text 
HAVING queries like '%,%' 
ORDER BY query_text

Its purpose is to identify potentially duplicate entries on the table by returning unique results per query_text value across all stores. Here's a sample of my output:

*************************** 12597. row ***************************
query_text: blue shirt
  store_id: 5
   queries: 2332223,2332233
*************************** 12598. row ***************************
query_text: blue shirt
  store_id: 11
   queries: 2202093,2202103
*************************** 12599. row ***************************
query_text: hat
  store_id: 5
   queries: 2458333,2458343
*************************** 12600. row ***************************
query_text: hats
  store_id: 5
   queries: 2207433,2207443

So you can see that, per record, I have a list of queries values. What I want to do now is concatenate those values into a single resulting value, so that my result set looks like this:

*************************** 1. row ***************************
   queries: 2332223,2332233,2202093,2202103,2458333,245834,2207433,2207443

My goal would be to leverage this as part of a DELETE operation where these duplicates are removed. But the structure of my query does not allow for that in SQL. I realize that I can write a script to parse the results and build a new query to remove records, but I want to accomplish this purely with SQL as a self-learning point.

(note: I would be adding to the delete query a date range, so that results are based on records which have an updated_at value of prior to 2014).

1

Well, I opted to stick with this query and use it to create a temporary table from which I could aggregate and delete duplicates.

Create Temporary Table

CREATE TEMPORARY TABLE IF NOT EXISTS qt AS (
  SELECT 
    DISTINCT query_text, 
    store_id, 
    GROUP_CONCAT(query_id ORDER BY query_id) AS queries 
  FROM catalogsearch_query 
  GROUP BY query_text, 
  store_id 
  HAVING queries like '%,%' 
  ORDER BY query_text
);

Reading Queries Into An Array

$write      = Mage::getSingleton('core/resource')->getConnection('core_write');
$readSql    = "SELECT queries FROM qt";
$results    = $write->fetchAll($readSql);
$ids        = array();

foreach ($results as $row) {
  $rowIds = explode(',', $row['queries']);

  // Preserve 1 record (with highest ID)
  sort($rowIds, SORT_NUMERIC);
  array_pop($rowIds);

  $ids = array_merge($ids, $rowIds);
}

Removing Duplicates

$deleteSql  = "DELETE FROM catalogsearch_query WHERE query_id IN (" . implode(',', $ids) . ");";
$results    = $write->query($deleteSql);

So it's less than ideal, and slow given the IN() on a WHERE clause. But for a one-off cleanup script, it gets the job done.

| improve this answer | |

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.