This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:autocomplete:sql [2011/01/24 21:42] els |
admin:indexing:autocomplete:sql [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 21: | Line 21: | ||
| The SQL behind AutoComplete is a simple query requesting grouped counts. For QuickText indexes, it is necessary to use an optimization setting so that one value is returned for each parsed word, rather than one value for each row. | The SQL behind AutoComplete is a simple query requesting grouped counts. For QuickText indexes, it is necessary to use an optimization setting so that one value is returned for each parsed word, rather than one value for each row. | ||
| - | The following SQL statement provides the results shown in the previous example: | + | === Initializing the Pull-Down Box === |
| - | <code sql> | + | The following SQL statement provides the results shown in the previous example. Depending on the size of the database, it may be important to not issue a SQL statement until the user has typed two or three letters. That can dramatically affect the number of rows retrieved, and also the time required to query the Omnidex indexes. |
| + | |||
| + | <code> | ||
| select FNAME, count(*) | select FNAME, count(*) | ||
| from LIST | from LIST | ||
| - | where FNAME='Wi*' | + | where FNAME='Wil*' |
| group by FNAME | group by FNAME | ||
| order by 2 desc | order by 2 desc | ||
| - | with optimization=distinct_key | + | with optimization=distinct_key; |
| </code> | </code> | ||
| This SQL statement will provide results that can then be placed into drop-down boxes as needed: | This SQL statement will provide results that can then be placed into drop-down boxes as needed: | ||
| - | <code sql> | + | <code> |
| - | > select FNAME, count(*) | + | |
| - | >> from LIST | + | |
| - | >> where FNAME='Wi*' | + | |
| - | >> group by FNAME | + | |
| - | >> order by 2 desc | + | |
| - | >> with optimization=distinct_k | + | |
| FNAME COUNT('*') | FNAME COUNT('*') | ||
| - | ------------------------ ---------- | + | ------------------------------------ |
| WILLIAM 118130 | WILLIAM 118130 | ||
| WILLIE 19307 | WILLIE 19307 | ||
| Line 50: | Line 45: | ||
| WILLIS 1661 | WILLIS 1661 | ||
| WILBUR 1660 | WILBUR 1660 | ||
| - | WINIFRED 1365 | ||
| WILBERT 1357 | WILBERT 1357 | ||
| WILSON 1351 | WILSON 1351 | ||
| WILFRED 1141 | WILFRED 1141 | ||
| - | WINSTON 946 | ||
| WILL 826 | WILL 826 | ||
| WILLA 800 | WILLA 800 | ||
| - | WINNIE 800 | ||
| WILFREDO 698 | WILFREDO 698 | ||
| WILLIAMS 626 | WILLIAMS 626 | ||
| WILEY 536 | WILEY 536 | ||
| WILDA 457 | WILDA 457 | ||
| - | WINFRED 445 | ||
| WILFORD 428 | WILFORD 428 | ||
| WILMER 367 | WILMER 367 | ||
| - | WINONA 366 | ||
| WILHELMINA 348 | WILHELMINA 348 | ||
| WILBURN 345 | WILBURN 345 | ||
| WILTON 280 | WILTON 280 | ||
| WILLY 216 | WILLY 216 | ||
| - | WINNIFRED 212 | ||
| - | WINDY 201 | ||
| - | WINFORD 176 | ||
| WILBER 175 | WILBER 175 | ||
| WILLENE 163 | WILLENE 163 | ||
| Line 79: | Line 66: | ||
| WILLETTE 104 | WILLETTE 104 | ||
| WILLIEMAE 59 | WILLIEMAE 59 | ||
| - | WINTER 58 | ||
| WILLODEAN 48 | WILLODEAN 48 | ||
| WILLENA 47 | WILLENA 47 | ||
| Line 85: | Line 71: | ||
| WILLOW 39 | WILLOW 39 | ||
| WILLETTA 35 | WILLETTA 35 | ||
| - | 41 rows returned | + | 32 rows returned |
| + | </code> | ||
| + | |||
| + | |||
| + | |||
| + | === Refining the Pull-Down Box === | ||
| + | |||
| + | As the user types more letters, the contents of the pull-down box need to be updated. There are two approaches that can be taken. The recommended approach is to retain the contents of the initial query in memory, and then filter and redisplay them as the user types letters. This is generally much faster than re-querying the database and reduces the load on system resources. | ||
| + | |||
| + | It is also possible to resubmit the query to the database with an additional letter added to the criteria, though care should be given to insure that performance does not degrade with this approach. | ||
| + | |||
| + | === Subsequent Searches === | ||
| + | |||
| + | As the user provides criteria to various fields, that criteria can also be included in the AutoComplete query. For example, the same statement above can be performed with criteria against the State and Last Name columns as shown below: | ||
| + | |||
| + | <code> | ||
| + | select FNAME, count(*) | ||
| + | from LIST | ||
| + | where STATE = 'NY' and | ||
| + | LNAME = 'Myers' and | ||
| + | FNAME='Wil*' | ||
| + | group by FNAME | ||
| + | order by 2 desc | ||
| + | with optimization=distinct_key; | ||
| + | </code> | ||
| + | |||
| + | The AutoComplete choices and their counts will be correspondingly reduced to those in the State of New York with a Last Name of Myers: | ||
| + | |||
| + | <code> | ||
| + | FNAME COUNT('*') | ||
| + | ------------------------ ---------- | ||
| + | WILLIAM 6 | ||
| + | WILDA 1 | ||
| + | WILLARD 1 | ||
| + | WILLIE 1 | ||
| + | WILMA 1 | ||
| + | WILMER 1 | ||
| + | 6 rows returned | ||
| </code> | </code> | ||