[Vtigercrm-developers] Storage of Multi-Select Items forQuicker MYSQL Searching
Neil Temperley
neil.temperley at exemail.com.au
Mon Jun 11 05:23:53 PDT 2007
Some Background:
We are a publicly funded (government/university) R&D organisation. Our
engagements and collaborations with industry drives our R&D directions and
provides pathways for commercialisation. So my company is not quite the
classic target customer for vtiger or Sugar.
Our key needs:
1) A single definite list of Contacts and Companies (Accounts).
2) Mail outs (news and invitations) to filtered lists of Contacts.
(Filtering on combinations of multi-select combo boxes). The ability to
slice and dice our Contacts in different ways is essential.
3) Track industry engagements/meetings/agreements (Opportunities).
4) Nice to be able to track which Companies/Contacts have attended various
events we hold, e.g. technology showcases, business briefings, short
courses. (A bit of a kludge, we are experimenting with using Sugar Projects
for this.)
5) Nice to get the CRM to do document management. We store important
meeting Notes and Emails under Opportunities. (Document management is not
perfect; we would like to find an attached document by text search.)
(BTW this would be a really nice feature for the future, that is an indexing
system (similar to Copernic or Google Desktop) that indexes Notes and
attachments in the database in the background. With this we could use the
CRM to do our document management. That is, you could find a meeting note
under an Opportunity (for example), or find it by a 'free text' search.)
Anyway, regarding Sugar vs vtiger...
Some of the keys issues for us were around Contact management. (We have
about 1400 Accounts and 2700 Contacts)
Some of Sugar's features that were important to us.
* Flexible export: export of a filtered selection (really useful as
workaround to any lack of functionality, e.g. filter, then export to Excel,
then do an email to the list.)
* Duplicate Management
- detection of duplicates.
- merge of duplicates (really nice, though it has bugs still :-( )
(We will have lots of contributors to the database. Avoiding and managing
duplicates is really important.)
* Mass Update and Mass Delete
(Nice to manage large numbers of Contacts - but needs to be used carefully.)
Some of vtiger's advantages:
* better, more flexible, advanced filtering for List Views.
* Tags (we really liked this)
* HTML editing of Notes (not that important to us but nice).
* Ajax (not that important to us)
Our IT department liked that Sugar was more established (with a larger
support base?) and therefore more likely to be around in the long term.
(This is probably a bit unfair, but that was their perception.)
That's about it,
Ciao,
Neil
_____
From: vtigercrm-developers-bounces at lists.vtigercrm.com
[mailto:vtigercrm-developers-bounces at lists.vtigercrm.com] On Behalf Of Jorge
Torres
Sent: Monday, 11 June 2007 11:57
To: vtigercrm-developers at lists.vtigercrm.com
Subject: Re: [Vtigercrm-developers] Storage of Multi-Select Items forQuicker
MYSQL Searching
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
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.472 / Virus Database: 269.8.13/840 - Release Date: 08/06/07
15:15
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20070611/b0a755ad/attachment-0003.html
More information about the vtigercrm-developers
mailing list