[Vtigercrm-developers] Storage of Multi-Select Items for Quicker MYSQL Searching

[Neil Temperley]

Dear Developers,

In the end my company went with Sugar, but I still keep an eye on vtiger
because I love you guys :-).

Anyway here is an idea that I will post on a Sugar forum, but I'd thought
I'd post it here first so that if you choose to adopt it you can't be
accused of copying :-).  While I haven't checked the vtiger code for a while
I suspect this issue is applicable to you too.

-----------------------------------

Storage of Multi-Select Items for Quicker MYSQL Searching

Consider a custom multi-select field with items:
cat
dog
horse
bobcat
cattle

An example of a selection of the above is stored in the database as a string
like this (ignore the ""):
"cat#,#horse#,#bobcat#,#cattle"

And if "cat" is the only item the string saved looks like:
"cat"

This storage scheme has some problems when it comes to doing an SQL searches
on strings formatted this way.  To search for uniquely for "cat" we have to
search for four different possibilities: (The MYSQL search string is shown
in ""):

1) cat at the beginning of the multi-select database string, "cat#%",
2) cat in the middle, "%#cat#%",
3) cat at the end, "%#cat", and finally,
4) cat by itself, "cat".

If there are 1000 records and none have "cat" selected, the search needs to
check all four conditions on each record before rejecting each as 'not
matching'.

However if we choose to store the selection differently we only need to
search for one match not four matches per record.

Choosing '#' as the bounding character, we store the multi-selected items as
a string like this instead (ignore the ""):
"#cat##horse##bobcat##cattle#"

And if "cat" is the only item the string saved looks like:
"#cat#"

The advantages of this system are:

* Now the one MYSQL search "%#cat#%" will match "cat" regardless of where it
is in the string or whether it is the only item.

* A side benefit is that is possible to delete (or replace) the "cat" item
using a simple PHP string operation, e.g.
str_replace( "#cat#", "", "#cat##horse##bobcat##cattle#" );

* Also MYSQL searches are simpler -- if/when you ever need to do a search
using a database admin tool.

Note that to search for 'no selection' you need to check for ## or an empty
string.

I suggest the best way to implement the change is with dedicated explode()
and implode() functions and using a minimum of hardwired strings.  Examples
are given below.

that's all,
regards and good luck,
Neil


Sample Code...

/* ########################################################## */ 
define("MULTIPLE_SELECT_BOUNDING_CHAR", '#');
define("MULTIPLE_SELECT_BOUNDING_CHAR_RE", '\#'); // Char for regexps.

/**
 * Formats a string of multi-select items for saving to a database.
 * 
 * @param array $multiple_select_array
 * @return string formatted multi-select string
 * 
 */
function MultipleSelectImplode( $multiple_select_array ) {
    $bounding_char = MULTIPLE_SELECT_BOUNDING_CHAR;
    return $bounding_char . implode("$bounding_char$bounding_char",
$multiple_select_array) . $bounding_char;
}

/**
 * Splits a formatted string of multi-select items into an
 array. Returns an empty array if there are no multi-select items.
 * 
 * @param string $multiple_select_string
 * @return array multi-select array
 * 
 */
function MultipleSelectExplode( $multiple_select_string ) {
    $bounding_char    = MULTIPLE_SELECT_BOUNDING_CHAR;
    $bounding_char_re = MULTIPLE_SELECT_BOUNDING_CHAR_RE; // Char for
regexps
    // Remove $bounding_char at each  end of the string:
    $trimmed_string = preg_replace( array("/^\s*$bounding_char_re/",
"/$bounding_char_re\s*$/"), '', 
			    $multiple_select_string );
    if ( empty($trimmed_string) ) {
        // (An explode() here would return a one element array with empty
element):
	return array();
    }
    return explode("$bounding_char$bounding_char", $trimmed_string);
}
/* ########################################################## */