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

[Jorge Torres]

Neil,

It is clear you like vtiger, I have one question, what made your company
choose Sugar instead of vtiger?, so sooner someone can make vtiger offer
such things.

Regards,

Jorge

On 6/10/07, Neil Temperley <neil.temperley at exemail.com.au> wrote:
>
> 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);
> }
> /* ########################################################## */
>
> _______________________________________________
> Reach hundreds of potential candidates - http://jobs.vtiger.com
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20070610/d5172d0a/attachment-0003.html