Help with db_delete using both LIKE and IN.. I'm trying to delete many rows whilst having a wildcard

Question

I'm writing a custom cache clearing module for blocks.

At the moment, I can delete cache for one user and their multiple languages.

$uid = 7094;
$module = 'custom_home_panes';
$block = 'custom_home_panes_news_group';
$theme = 'framework';
$table = 'cache_block';

$cid_start = $module . ':' . $block . ':' . $theme . ':';
// Wildcard in middle for language code
$cid_end = ':u.' . $uid;

$delete = db_delete('cache_block')
  ->condition('cid', db_like($cid_start) . '%%' . db_like($cid_end), 'LIKE')
  ->execute();

But I'll need to do this for an array of UIDs and I believe I need to use IN like the snippet below..

->condition('uid', array(1,5,7),'IN')

I thought I could add what I did to an array and got this far.. The LIKE is really confusing me here and I think I'm going about it the wrong way.

foreach ($uids as $uid) {
    $queries[] = db_like($module . ':' . $block . ':' . $theme . ':') . '%%' . db_like(':u.' . $uid), 'LIKE';
}

The end result should be a query that removes records such as the ones below.

custom_home_panes:custom_home_panes_news_group:framework:vi:u.7250
custom_home_panes:custom_home_panes_news_group:framework:en:u.7250
custom_home_panes:custom_home_panes_news_group:framework:vi:u.7253
custom_home_panes:custom_home_panes_news_group:framework:en:u.7253

Any help appreciated.. My SQL skills are abysmal. Thanks!

Answer 1

You can't combine LIKE and IN in the way you are attempting.

You will need a run a different query for each UID.

E.g.

$cid_base_pattern = db_like($module) . ':' . db_like($block) . ':' . db_like($theme) . ':%:u.';

foreach ($uids as $uid) {
    db_delete('cache_block')
      ->condition('cid', $cid_base_pattern . $uid, 'LIKE')
      ->execute();
}