Using full-text searches in AppGini apps

I recently received a very interesting question from an AppGini user regarding implementing full-text searches in AppGini apps. The user had a table containing millions of records, and normal search operations on a text field took too long (about 1.5 minutes according to the user). When he tried adding a full-text index on that field, and using full-text search, search time dropped dramatically to several milliseconds. Unfortunately, AppGini doesn’t yet support full-text searches. So, in this post, we’ll try to address that using hooks .

Full-text search in MySQL/MariaDB databases lets users run full-text queries against character-based data in a table. You must create a full-text index on the table before you run full-text queries on that table. This can be done in phpMyAdmin, or any other MySQL admin utility, by executing a command similar to this:

1

ALTER TABLE `customers` ADD FULLTEXT INDEX `Address` (`Address`);

The above would add a full-text index on the ‘Address’ field of the ‘customers’ table. Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.) Full-text indexes can be created only for CHAR, VARCHAR, or TEXT columns. If you’re using MariaDB, check full-text compatibility here .

Having created a full-text index on a field, we’d like now to change normal search filters performed on that field into full-text searches. Normal search using the LIKE operator looks something like this:

1

`customers`.`Address` LIKE '%place%'

This might get very slow for TEXT fields in huge tables that have millions of records. Applying a normal index on the field won’t fix this since normal indexes work only on the first 765 bytes of the field. If you’re searching beyond those first 765 bytes, you’re out of luck with normal indexes. But full-text indexes come to the rescue in this scenario. The full-text search clause in that case would look something like this:

1

MATCH(`customers`.`Address`) AGAINST ('place' IN BOOLEAN MODE)

The question now is how to apply the above search clause in your AppGini app? We’ll walk through one method that is easy to apply using hooks. First, we’ll add 2 new functions to the hooks/__global.php file. We can add them anywhere, but, for simplicity’s sake, let’s append them to the end of the file:

 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

function use_full_text_search($field_index, $field_name, &$options) {
	// if we have a LIKE/NOT LIKE filter on full-text field,
	// 1. remove it from REQUEST
	// 2. inject a hint in REQUEST to re-apply the filter info later in _footer hook
	// 3. add a full-text search on field in the WHERE clause
	if(!isset($_REQUEST['FilterField'])) return;

	// avoid this procedure in filter page
	if(!empty($_REQUEST['Filter_x'])) return;

	// also avoid it when emptying filters by clicking 'Show all'
	if(!empty($_REQUEST['NoFilter_x'])) return;

	foreach($_REQUEST['FilterField'] as $i => $field) {
		if($field != $field_index) continue; // not our full-text field
		
		$combine = $_REQUEST['FilterAnd'][$i];
		$op = $_REQUEST['FilterOperator'][$i];
		$search = $_REQUEST['FilterValue'][$i];
		
		if($combine != 'or') $combine = 'and';
		if(!in_array($op, array('like', 'not-like'))) continue;
		if(!$search) continue;

		// remove filter from REQUEST
		$_REQUEST['FilterAnd'][$i] =
		$_REQUEST['FilterField'][$i] =
		$_REQUEST['FilterOperator'][$i] =
		$_REQUEST['FilterValue'][$i] = '';

		// inject hint to REQUEST to re-apply the filter later in _footer hook
		$_REQUEST['full-text'] = json_encode(array(
			'i' => $i,
			'FilterAnd' => $combine,
			'FilterField' => $field_index,
			'FilterOperator' => $op,
			'FilterValue' => $search
		));

		// add a full-text search on field in the WHERE clause
		if(!$options->QueryWhere)
			$options->QueryWhere = ' where ';
		else
			$options->QueryWhere .= ' and ';
		$options->QueryWhere .= ($op == 'not-like' ? 'NOT ' : '') . 
			'MATCH(' . $field_name . ') AGAINST (\'' . makeSafe($search) . '\' IN BOOLEAN MODE) ';
	}
}

function reapply_pre_full_text_filter($footer) {
	// if we have a REQUEST hint added from _init hook,
	// re-apply the removed filter
	if(!empty($_REQUEST['full-text'])) {
		$filter = json_decode($_REQUEST['full-text']);
		if(!$filter) return $footer;
		if(empty($filter->FilterAnd)) return $footer;
		if(empty($filter->FilterOperator)) return $footer;
		if(empty($filter->FilterField)) return $footer;
		if(empty($filter->FilterValue)) return $footer;

		// add hidden inputs to the form for the removed filter
		ob_start(); ?>
		<script>$j(function() {
			var filter = <?php echo $_REQUEST['full-text']; ?>;
			var addHidden = function(i, name, val) {
				$j('<input type="hidden"></input>')
					.attr('name', name + '[' + i + ']')
					.attr('value', val)
					.appendTo('form');
			};

			addHidden(filter.i, 'FilterAnd', filter.FilterAnd);
			addHidden(filter.i, 'FilterField', filter.FilterField);
			addHidden(filter.i, 'FilterOperator', filter.FilterOperator);
			addHidden(filter.i, 'FilterValue', filter.FilterValue);
		})</script><%%FOOTER%%>
		<?php
		$footer .= ob_get_clean();

		return $footer;
	}
}

The above defines 2 functions: use_full_text_search() and reapply_pre_full_text_filter() . The first function should be called inside the init hook for our table, and what it does is prevent our AppGini application from performing normal search with LIKE operator on our full-text field. This is done by removing any defined LIKE searches on that field.

The second function should be called inside the footer hook . Its purpose is to restore the removed LIKE search so that it’s still visible and configurable in the filters page. So, basically, we’re removing the filter before our AppGini app constructs the SQL query, and then adding it back after the query has been executed.

Applying the above 2 functions in our table hooks

After defining the functions above in global hooks, we should now use them in our table. Let’s continue assuming that our table is named ‘customers’, and our full-text field is named ‘Address’. We also need to know the index (order) of that field in the table, as defined in AppGini. For example, if that field is the fifth field, its index is 5, as shown in this screenshot:

Now that we know all the necessary information, let’s open the hooks/customers.php hook file in our preferred text editor, and let’s add this line to the customers_init() hook before the return statement:

1

use_full_text_search(5, '`customers`.`Address`', $options);

‘5’ is the index of the field as explained above, and then we pass the field name, including the table name. Next, let’s add this line to the customers_footer() hook before the return statement:

1

$footer = reapply_pre_full_text_filter($footer);

That’s all we need to do. Now, whenever any user defines a LIKE or NOT LIKE filter on the ‘Address’ field, our code would automatically change that to a full-text search on the field, dramatically reducing the query time.

Limitations/possible enhancements

This is a quick and experimental post that I wrote hastily in response to a user question. There are several limitations for the above code listed below. The purpose of the post is to act as a starting point for anyone who’d like to perform full-text search in AppGini, rather than a fully-working solution. Here are the limitations I’m aware of:

• The above code won’t work unless the field has a full-text index applied to it.
• If all the data in the concerned field has a length of less than 756 bytes (which translates roughly to 255 characters if using Unicode encoding), it’s much simpler to apply a normal index and avoid all the above procedure. The above method works best with longer fields.
• The above code assumes only one single full-text field per table. If you have more than one field, you might need to modify the code to adapt to all fields, but I’ll leave this as an exercise to you 😉
• The above code probably works fine if you define only one filter. It might not work when defining multiple filters, but I haven’t investigated this. Might revisit this post later and fix that, or if you have suggestions, please feel free to post your comment below.
• Moreover, the above code won’t work with quick search. I welcome contributions for applying full-text search to quick search 🙂