Lasso Soft Inc. > Home

[fulltext]

Linkfulltext
AuthorJohan Solve
CategoryDatabase
Version6.x
LicensePublic Domain
Posted17 Jan 2006
Updated06 Sep 2006
More by this author...

Description

This tag emulates FileMaker's convenient fulltext search behavior with MySQL. It searches for words beginning with the words in the query, appearing in any order, with either AND or OR in one or multiple fields, including support for "phrase searches", using either ldml or sql inlines. However it does not take advantage of any MySQL index so it's best suited for databases that are not too large (some 10,000 records work fine).

Search for dog cat will find caterpillar in a doghouse but not scattered dogs. Also supports phrases in search request such as "black dog" which will find black dog or black doghouse but not black spotted dog

The tag works with Lasso 6 and later.

Version 1.0.1 2004-02-24 Removed ` around SQL field names
Version 1.0 2004-02-12

Sample Usage

normal search with default AND operator between search words, to use in ldml inline (default)
	inline: -database..., 
		(fulltext: 'fieldname'='search string'), 
		-search;
search with OR operator between search words:
	inline: -database..., 
		(fulltext: 'fieldname'='search string', -or), 
		-search;
make the search span multiple fields as if they were one concatenated field
	inline: -database..., 
		(fulltext: (array: 'fieldname1', 'fieldname2', ...)='search string'), 
		-search;
use fulltext in SQL inline
	inline: -database..., 
		-sql='SELECT * FROM table WHERE ' 
			+ (fulltext: 'fieldname'='search string', -sql), 
		-search;


Test form:

One field, ldml:
[(fulltext: 'searchfield'=(action_param: 'text')) -> join: '
']
Multiple fields, ldml:
[(fulltext: (array: 'subject', 'body')=(action_param: 'text')) -> join: '
']
Multiple fields, ldml, OR operator: [(fulltext: (array: 'subject', 'body')=(action_param: 'text'), -or) -> join: '
']

One field, sql:
[fulltext: 'searchfield'=(action_param: 'text'), -sql]
Multiple fields, sql:
[fulltext: (array: 'subject', 'body')=(action_param: 'text'), -sql]
Multiple fields, sql, OR operator: [fulltext: (array: 'subject', 'body')=(action_param: 'text'), -sql, -or]

Source Code

Click the "Download" button below to retrieve a copy of this tag, including the complete documentation and sample usage shown on this page. Place the downloaded ".inc" file in your LassoStartup folder, restart Lasso, and you can begin using this tag immediately.

 (get: 1) -> name),
		'temp_searchstring'=(params -> (get: 1) -> value);
	local: 'fieldname'=#temp_fieldname,
		'searchstring'=#temp_searchstring,
		'thisfield'='';
	if: #fieldname -> type != 'array';
		#fieldname = (array: #fieldname);
	/if;

	// Set search mode -- wordbeginswith is more expensive. By setting this to false we will search 
	// for words anywhere in the field, not just in the beginning of words. Set it to false for best performance.
	// Set it to true for best user experience. 
	local: 'wordbeginswith'=true;

	// Define word separators to be able to handle wordbeginswith searches.
	// The more word separators, the more expensive search.
	// For better performance, reduce this array. 
	local: 'wordseparators'=(array: ' ', '.', ',', ':', '-', '"', '\'', 
			'(', '/', '&', '+', '\r', '\n', '[', ']', '<', '>', '{', '}');
	
	if: params >> '-or';
		local: 'operator'='OR';
	else;
		local: 'operator'='AND';
	/if;
	if: params >> '-sql';
		local: 'usesql'=true;
	else;
		local: 'usesql'=false;
	/if;
	
 	// HANDLE PHRASES
	
	// extract phrases from search string
	local: 'phrases'=(string_findregexp: #searchstring, -find='"(.*?)"');
	
	// remove phrases from search string
	#searchstring = (string_replaceregexp: #searchstring, -find='".*?"', -replace='');
	
	// clean up remaining search string to make it strictly word-space-word etc
	// replace any sequence of non-word characters with a single space
	#searchstring = (string_replaceregexp: #searchstring, -find='[\\s,.()&/!%-]+', -replace=' ');

	// make the remaining search string into an array, word by word (separated by space)
	#searchstring = #searchstring -> split: ' ';
	
	// add the phrases
	if: (#phrases -> type) == 'array' && (#phrases -> size) >1;
		loop: -loopfrom=2, -loopto=(#phrases -> size), -loopincrement=2;
			//element 2, 4, 6 etc in the array are the phrases we want
			#searchstring -> (insert: #phrases -> (get: loop_count));
		/loop;
	/if;
	
	// clear blank words, step backwards through array to avoid tripping over collapsing array
	loop: -loopfrom=(#searchstring -> size), -loopto=1, -loopincrement = (-1);
		if: (#searchstring -> (get: loop_count)) == '';
			#searchstring -> (remove: loop_count);
		/if;
	/loop;

	if: #usesql;
		local: 'output'=(string),
			'searchword_sql'=(string),
			'separator_sql'=(string);
		if: #wordbeginswith;
			iterate: #searchstring, (local: 'searchword');
			#searchword_sql=(encode_sql: #searchword);
			// escape MySQL wildcard characters
			#searchword_sql = (string_replace: (string_replace:
					#searchword_sql, 
					-find='%', -replace='\\%'), 
				-find='_', -replace='\\_');
				#output += ' (';
				iterate: #fieldname, #thisfield;
					#output += 
						 ' (' + #thisfield + ' LIKE "' + #searchword_sql + '%" ';
					iterate: #wordseparators, (local: 'separator');
						#separator_sql=(encode_sql: #separator);
						#output += 
							' OR ' + #thisfield + ' LIKE "%' + #separator_sql + #searchword_sql + '%" ';
					/iterate;
					#output += ') OR ';
				/iterate;
				#output -> removetrailing: ' OR ';
				#output += ') ' + #operator;
			/iterate;
		else;
			iterate: #searchstring, (local: 'searchword');
				#searchword_sql=(encode_sql: #searchword);
				// escape MySQL wildcard characters
				#searchword_sql = (string_replace: (string_replace:
					#searchword_sql, 
					-find='%', -replace='\\%'), 
				-find='_', -replace='\\_');
				#output += ' (';
				iterate: #fieldname, #thisfield;
					#output += 
						  '' + #thisfield + ' LIKE "%' + #searchword_sql + '%" OR ';
				/iterate;
				#output -> removetrailing: ' OR ';
				#output += ') ';
				#output +=  #operator;
			/iterate;
		/if;
		#output -> removetrailing: #operator;
	else; // ldml inline -- default
		local: 'output'=(array);
		#output -> (insert: '-opbegin'=#operator);
		if: #wordbeginswith;
			iterate: #searchstring, (local: 'searchword');
				#output -> (insert: '-opbegin'='or');
				iterate: #fieldname, #thisfield;
					#output -> (insert: '-op'='bw');
					#output -> (insert: #thisfield=#searchword);
					iterate: #wordseparators, (local: 'separator');
						#output -> (insert: '-op'='cn');
						#output -> (insert: #thisfield=#separator + #searchword);
					/iterate;
				/iterate;
				#output -> (insert: '-opend'='or');
			/iterate;
		else;
			iterate: #searchstring, (local: 'searchword');
				if: #fieldname -> size > 1;
					#output -> (insert: '-opbegin'='or');
				/if;
				iterate: #fieldname, #thisfield;
					#output -> (insert: '-op'='cn');
					#output -> (insert: #thisfield=#searchword);
				/iterate;
				if: #fieldname -> size > 1;
					#output -> (insert: '-opend'='or');
				/if;
			/iterate;
		/if;
		#output -> (insert: '-opend'=#operator);
	/if;
	return: #output;
/define_tag;

?>

Comments

No comments

Please log in to comment

Subscribe to the LassoTalk mail list

LassoSoft Inc. > Home

 

 

©LassoSoft Inc 2015 | Web Development by Treefrog Inc | PrivacyLegal terms and Shipping | Contact LassoSoft