create new tag
, view all tags

Nested Searches or Hash Searches

2016-03-12 - 20:41:44 by JaniHamalainen in General
I'm a fairly new user to TWiki and upon encountering a scenario where I needed to do a complicated search I remembered reading about the ability to do nested searches. I implemented the nested search only to find out that it was slower than I expected. It took me awhile to find out there was another option, using hashes as found in SpreadSheetPlugin. Using this approach, my page load time was reduced to about a third when compared to a nested search.

The purpose of this blog entry is to show an example of a nested search and then the same search using hashes.

Let's Set up an Example to Follow


  • I have a "Database" created in TWiki that contains Recipes. See BlogEntry201009x1 for an example of how to create a "Database".
  • I'm very particular about my Spices and have another "Database" created that provides particulars for each spice.


  • Provide a list (generated with SEARCH) of all my Recipes and include the Spices required by each particular recipe and the quantity of spice remaining in inventory.

Let's start with the form stored in a Recipe topic:


Name Type Size Values Tooltip message Attributes
MealName text 60   The name of the recipe  
RecipePDF file       maxsize{15000K} pattern{[a-z,A-Z 0-9 -_]{1,}}
SpicesUsed select+multi+values 5 Spice Search Display all spices in my SpiceDB  

The form contains:

  • A text field where I give the recipe a name.
  • The recipe itself (PDF, jpg, etc). This formfield requires FileUploadPlugin.
  • The spices used by the recipe. A search is used to create a comma separated list of all spices from the SpiceDB topics

 %SEARCH{ "name~'SpiceDB????'" type="query" nonoise="on" sort="formfield(SpiceName)" 
separator=", "}% 

Since we've mentioned the SpiceDB let's show the form:


Name Type Size Values Tooltip message Attributes
SpiceName text 60   The name of the spice  
SpiceInventory text 60   The quantity of spice in inventory  

This form contains:

  • A text field with the spice name
  • A text field with the amount of spice in inventory


  • I have a series of topics (RecipeDB001, RecipeDB002, RecipeDB003, etc) that each contain a RecipeForm and recipe (PDF, jpg, etc) attached to the topic.
  • I also have a series of topics (SpiceDB001, SpiceDB002, SpiceDB003, etc) that each contain a SpiceForm.

Using a Nested Search

My initial search thought was to create a nested search:

 %SEARCH{ "name~'RecipeDB???'" type="query" nonoise="on"
format="| [[%PUBURL%/%WEB%/$topic/$percntCALC{\"$LISTITEM($LISTSIZE($query(attachments.name)), $query(attachments.name))\"}$percnt][$formfield(MealName)]] | 
$formfield(SpicesUsed) | $percntSEARCH{\"name~'SpiceDB???' AND fields[name='SpiceName'].value='$formfield(SpicesUsed)'\" type=\"query\" nonoise=\"on\" 
format=\"[[$dollartopic][$dollarformfield(SpiceInventory)]]\"}$percnt  |  [[$topic][Edit]]   |  $percntCALC{$FORMATTIMEDIFF(1,min,$EVAL($TIME()-$query(info.date)))}$percnt ago  |" 
header="|  *Meal Name*  |  *Spices Required*  |   *Inventory*   |   *Edit Recipe*   |  *Last Updated*  |"

Breaking Down the Search

Searches all topics starting with RecipeDB using a query search:

 %SEARCH{ "name~'RecipeDB???'" type="query" nonoise="on" 

Display the MealName as a link to the latest attachment (by date). It uses $LISTSIZE to find the quantity of attachments in a topic and then requests the attachment with the highest index (which corresponds to the last attachment that was uploaded).

 [[%PUBURL%/%WEB%/$topic/$percntCALC{\"$LISTITEM($LISTSIZE($query(attachments.name)), $query(attachments.name))\"}$percnt][$formfield(MealName)]] 

Display the value of the formfield named SpicesUsed for each topic


Performs the nested search within each search hit for all topics starting with SpiceDB and having a form with a field called SpiceName that has a value matching the value of the formfield SpicesUsed in the parent search. The search then returns the inventory level of the spice and a link to the specific SpiceDB??? topic.

 $percntSEARCH{\"name~'SpiceDB???' AND fields[name='SpiceName'].value='$formfield(SpicesUsed)'\" type=\"query\" nonoise=\"on\" 
Note: The above search will not work if there are multiple values in the SpicesUsed field.

Provide a link to each Recipe topic if editing is required:


Provides a neat way of displaying when the recipe topic was last updated (uses SpreadSheetPlugin)

$percntCALC{$FORMATTIMEDIFF(1,min,$EVAL($TIME()-$query(info.date)))}$percnt ago 

As mentioned above, I was nervous that the search was taking longer than I would have expected. I stumbled upon a document in the Support Web (Display form field of a referenced topic in formatted search) that mentioned the concept of a hash using SpreadSheetPlugin

Using Hashes Instead

Instead of doing a nested search, a spreadsheet has can store data in a hash with the syntax $SETHASH( name, key, value ). This means that for every spice (key)in my collection I can store the inventory level matching that spice as the value.

 %SEARCH{ "name~'SpiceDB????'" type="query" nonoise="on"
format="$percntCALCULATE{$SETHASH(SpiceQty, $formfield(SpiceName), $formfield(SpiceInventory)) $SETHASH(SpiceTopic, $formfield(SpiceName), $topic)}$percnt 

Now, I have a hash created where, instead of doing a search for inventory level of a particular spice, I can simply call $GETHASH(SpiceQty, Cinnamon) and it will return 4 cups (which corresponds to my inventory level). I also have another hash created that can find out the topic of a particular spice by $GETHASH(SpiceTopic, Cinnamon) which will return SpiceDB005.

The search command is then changed to:

 %SEARCH{ "name~'RecipeDB???'" type="query" nonoise="on"
format="| [[%PUBURL%/%WEB%/$topic/$percntCALC{\"$LISTITEM($LISTSIZE($query(attachments.name)), $query(attachments.name))\"}$percnt][$formfield(Part Number)]] |
$percntCALCULATE{$LISTJOIN(<br>,$LISTEACH([[$GETHASH(SpiceTopic,$item)][$item]],$formfield(SpicesUsed))) | 
$LISTJOIN(<br>,$LISTEACH($GETHASH(ProgStatus,$item),$formfield(ProgramNumber)))}$percnt |  [[$topic][Edit]]   |  
$percntCALC{$FORMATTIMEDIFF(1,min,$EVAL($TIME()-$query(info.date)))}$percnt ago  |" 
header="|  *Meal Name*  |  *Spices Required*  |   *Inventory*   |   *Edit Recipe*   |  *Last Updated*  |"}%

Most of the content is identical to the search describe in the nested search example above so the focus will be only on what's different.

Our goal here is to display the name of the spice, that when clicked, goes to the topic for that specific spice. We also want to display the inventory level of that particular spice. The structure is a bit more complicated because we need to be able to handle multiple spices for each recipe if needed. For example: the recipe for Pancakes may require the spices Cinnamon and Vanilla (displayed as Cinnamon, Vanilla in the formfield(SpicesUsed)).

$percntCALCULATE{$LISTJOIN(<br>,$LISTEACH([[$GETHASH(SpiceTopic,$item)][$item]],$formfield(SpicesUsed))) | 

takes a list that is separated by commas and replaces the comma with another character. In this case a line break.

takes a list and applies a formula to the list.

takes the input list of $formfield(SpicesUsed) and applies a formula. In this case the formula is [[$GETHASH(SpiceTopic,$item)][$item]] which uses the hash that accepts the name of the spice and returns the topic for that spice. The $GETHASH portion provides the spice topic while $item provides the name of the spice for the link. This formula is applied to each spice (or element ($item)) in $formfield(SpicesUsed). This completes $LISTEACH which provides a list such as Cinnamon, Vanilla, Nutmeg

The outer step is $LISTJOIN which takes the comma separated list and replaces it with line breaks.

The next part of the search is identical to the previous with the exception that the formula does not create links for the inventory level.


The Result

The result is a nice table showing a list of all your recipes along with spices used and their quantities.

Note: The links do not work.

Meal Name Spices Required Inventory Edit Recipe Last Updated
Pancakes Cinnamon
4 cups
10 sticks
45 grams
Edit 10 days ago
Chili Paprika 50 grams Edit 10 hours ago
Lentil Soup Coriander
10 grams
20 grams
Edit 5 hours ago

Explore VarCachePlugin which allows for caching a page. This could also be a potential solution to speed up page loading.


Thank you Jani, nice example explaining a complex task in an easy way!

I added a spices image as the blog icon, shown in the blog home.

-- Peter Thoeny - 2016-03-12


Edit | Attach | Watch | Print version | History: r2 < r1 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r2 - 2016-03-12 - PeterThoeny

Twitter Delicious Facebook Digg Google Bookmarks E-mail LinkedIn Reddit StumbleUpon    
  • Help
  • Learn about TWiki  
  • Download TWiki
This site is powered by the TWiki collaboration platform Powered by Perl Hosted by OICcam.com Ideas, requests, problems regarding TWiki? Send feedback. Ask community in the support forum.
Copyright © 1999-2017 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.