How can I bulk update the Matrix search index without re-saving each entry?

Question

I have a site that has 100's of products. I just enabled a custom field (Matrix) to be searchable and now the only way to update the index is by re-saving each entry.

I'm not really familiar with this part of EE, but would a query/script be able to update the index? Or is there an addon out there that can do this that I'm not aware of?

Answer 1

I actually had to do this recently. It's not too difficult to grab all that Matrix data, concatenate it, and save it to your field.

First, backup your database (!). Next, create a new template, enable PHP on it, then put this in it:

<?php

$this->EE =& get_instance();

$channel_id = 1;
$field_id = 20;
$matrix_cols = array(
    'col_id_1',
    'col_id_2',
    'col_id_9'
);

$entries = $this->EE->db->query("
    SELECT entry_id 
    FROM exp_channel_titles 
    WHERE channel_id = $channel_id
");
if($entries->num_rows() > 0)
{
    $cols = implode(", ", $matrix_cols);
    foreach($entries->result_array() as $entry)
    {
        extract($entry);
        $matrix = $this->EE->db->query("
            SELECT $cols 
            FROM exp_matrix_data 
            WHERE field_id = $field_id 
            AND entry_id = $entry_id
        ");
        if($matrix->num_rows() > 0)
        {
            $data = '';
            foreach($matrix->result_array() as $row)
            {
                foreach($matrix_cols as $col)
                {
                    $data .= $row[$col].' ';
                }
            }
            echo '<p>Search data for entry '.$entry_id.': '.$data.'</p>';
            $this->EE->db->query(
                $this->EE->db->update_string('exp_channel_data', array('field_id_'.$field_id => $data), "entry_id = $entry_id")
            );
        }
    }
}

?>

Replace the data inside the three variables at the top with your relevant data, then load your template in the browser. Should fix you up.

Answer 2

EE doesn’t have a search index; it just queries exp_channel_data directly on every search.

The issue is that Matrix primarily saves its data in exp_matrix_data, not exp_channel_data. It’ll dump the content from any columns listed as Searchable into exp_channel_data precicely so that it’s discoverable by EE search, but only when an entry is saved. It does not provide a way to update exp_channel_data across all entries when the Searchable settings are changed.

We’ll have to fix this with an update. I’ll update this thread when that happens.