Alter search query condition for dataset in Drupal 7

Question

I am very new to Drupal and attempting to build a module that will allow admins to tag nodes with keywords to boost nodes to the top of the search results.

I have a separate DB table for the keywords and respective node IDs. This table is UNIONed with the search_index table via hook_query_alter...

function mos_search_result_forcer_query_alter(QueryAlterableInterface &$query) {
    if (get_class($query) !== 'PagerDefault') { //<< check this because this function will mod all queries elsewise
        return;
    }

    // create unioned search index result set...
    $index = db_select('search_index', 's');

    $index->addField('s', 'sid');
    $index->addField('s', 'word');
    $index->addField('s', 'score');
    $index->addField('s', 'type');

    $msrfi = db_select('mos_search_result_forcer', 'm');

    $msrfi->addField('m', 'nid', 'sid');
    $msrfi->addField('m', 'keyword', 'word');
    $msrfi->addExpression('(SELECT MAX(score) + m.id / (SELECT MAX(id) FROM {mos_search_result_forcer}) FROM {search_index})', 'score');
    $msrfi->addExpression(':type', 'type', array(':type' => 'node'));

    $index->union($msrfi);

    $tables =& $query->getTables();

    $tables['i']['table'] = $index;

    return $query;
}

Drupal then generates the almost correct query...

SELECT 
    i.type AS type, i.sid AS sid, SUM(CAST('10' AS DECIMAL) * COALESCE(( (12.048628015788 * i.score * t.count)), 0) / CAST('10' AS DECIMAL)) AS calculated_score 
FROM (
    SELECT 
        s.sid AS sid, s.word AS word, s.score AS score, s.type AS type 
    FROM 
        search_index s 
    UNION SELECT 
        m.nid AS sid, m.keyword AS word, (
            SELECT 
                MAX(score) + m.id / (SELECT MAX(id) FROM mos_search_result_forcer) 
            FROM 
                search_index
        ) AS score, 'node' AS type 
    FROM 
        mos_search_result_forcer m
) i 
INNER JOIN node n ON n.nid = i.sid 
INNER JOIN search_total t ON i.word = t.word 
INNER JOIN search_dataset d ON i.sid = d.sid AND i.type = d.type 
WHERE (n.status = '1') 
AND( (i.word = 'turtles') )
AND (i.type = 'node') 

/* this is the problem line... */
AND( (d.data LIKE '% turtles %' ESCAPE '\\') )
/* ...end problem line */

GROUP BY i.type, i.sid 
HAVING (COUNT(*) >= '1') 
ORDER BY calculated_score DESC 
LIMIT 10 OFFSET 0

...I need that "problem line" to read...

AND( (d.data LIKE '% turtles %' ESCAPE '\\') OR (d.sid IN (SELECT nid FROM mos_search_result_forcer)) )

...what hook can I use to add that OR condition?

• I don't want to hack Drupal's core.
• I don't want to change the union/subqueries (not my decision).
• I will optimize queries later - functionality is more important.

Thanks, smart people!

Answer 1

The basic principle is to grab the conditions array, loop through and find the index for the problem condition, remove it, then re-add an identical condition along with the new one as part of a db_or()

This is un-tested and most likely won't work verbatim, but it should give you a starting point:

$conditions =& $query->conditions();
$index = FALSE;
$removed_condition = NULL;

for ($i = 0, $l < count($conditions); $i < $l; $i++) {
  if ($conditions[$i]['field'] == 'd.data' && $conditions[$i]['operator'] == 'LIKE') {
    $index = $i;
    $removed_condition = $condition;

    break;
  }
}

if ($index !== FALSE) {
  unset($conditions[$index]);

  $sub_query = db_select('mos_search_result_forcer')->fields('mos_search_result_forcer', array('nid'));
  $new_condition = db_or()
    ->condition('d.data', $removed_condition['value'], 'LIKE')
    ->condition('d.sid', $sub_query, 'IN');

  $query->condition($new_condition);
}

Answer 2

Thanks to some helpful suggestions from @Clive with hook_module_implements_alter, and a lot of trial and error, I have finally solved this issue.

Here is the final code...

function mos_search_result_forcer_module_implements_alter(&$imps, $hook) {
    if ($hook !== 'query_alter' || !array_key_exists('mos_search_result_forcer', $imps)) {
        return;
    }

    $imp = $imps['mos_search_result_forcer'];

    unset($imps['mos_search_result_forcer']);

    $imps['mos_search_result_forcer'] = $imp;
}

function mos_search_result_forcer_query_alter(QueryAlterableInterface &$query) {
    if (get_class($query) !== 'PagerDefault') { //<< check this because this function will mod all queries elsewise
        return;
    }

    // create unioned search index result set...
    $index = db_select('search_index', 's');

    $index->addField('s', 'sid');
    $index->addField('s', 'word');
    $index->addField('s', 'score');
    $index->addField('s', 'type');

    $msrfi = db_select('mos_search_result_forcer', 'm');

    $msrfi->addField('m', 'nid', 'sid');
    $msrfi->addField('m', 'keyword', 'word');
    $msrfi->addExpression('(SELECT MAX(score) + m.id / (SELECT MAX(id) FROM {mos_search_result_forcer}) FROM {search_index})', 'score');
    $msrfi->addExpression(':type', 'type', array(':type' => 'node'));

    $index->union($msrfi);

    $tables =& $query->getTables();

    $tables['i']['table'] = $index;

    // needs special "or" condition to keep from filtering out forced resutls...
    class MSRFPagerDefaultHelper extends PagerDefault { //<< override to gain access to protected props
        static function msrfHelp(PagerDefault &$pagerDefault) {
            $searchQuery =& $pagerDefault->query;

            MSRFSearchQueryHelper::msrfHelp($searchQuery);
        }
    }

    class MSRFSearchQueryHelper extends SearchQuery { //<< override to gain access to protected props
        static function msrfHelp(SearchQuery &$searchQuery) {
            $conditions =& $searchQuery->conditions;

            $condition = db_or()->condition($conditions)->condition('d.sid', db_select('mos_search_result_forcer')->fields('mos_search_result_forcer', array('nid')), 'IN');

            $searchQuery->conditions = $condition;
        }
    }

    MSRFPagerDefaultHelper::msrfHelp($query);

    return $query; //<< i don't think this is needed as var is reffed - just for good measure, i guess
}