[fulltext] at tagSwap.net: Open Source Lasso Code

[fulltext]

Description

Link: [fulltext]

Author: Johan Sölve

Category: Database

Version: 6.x

License: Public Domain

Posted: Jan. 17, 2006

Updated: Sep. 06, 2006

More by this author...

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

Parameters

none

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:
<form action="[response_filepath]">
<input type="text" name="text" value="[action_param: 'text']">
</form>

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

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

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145

<?lassoscript
define_tag: 'fulltext';
	
	local: 'temp_fieldname'=(params -> (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