Tags:
create new tag
, view all tags
In the hope that it might inspire someone to (1) try out the new query language in MAIN or , even better (2) help refine it into a more comprehensive tutorial, here's a really basic intro to writing queries in TWiki.

How to write Queries

TWiki 4.2 has a new way to do searches; the query switch. This lets you write 'database style' searches over your topics. The big advantage of this type of search is that it is a lot simpler to and more intuitive than writing regular expression searches. It is also a key technology on the road to database backends, and better performance for TWiki.

This topic is a simple tutorial for writing queries in TWiki. By way of illustration we're going to develop a simple TWiki application using queries. You can build this application in your own Sandbox web.

The full details of the syntax used for queries is described in QuerySearch.

The Application

The application we will use is a party planner. The requirements are simple:
  • Track orders
    • Everything we order for the party (balloons, silly hats, party poppers etc) has to be tracked
  • Track invites
    • Each invite we send needs to be accepted. We'd like to know who is coming to the party.

We're going to build this application using TWikiForms; each order, and each invite, will be a new topic, each with a special form attached to it. The forms we need are OrderForm and InvitationForm.

OrderForm

Name Type Size Values Tooltip message Attributes
What text 60   What was ordered  
Supplier text 60   Name of supplier  
Cost text 30   What it costs  
Status select 3 Required,Ordered,Delivered Status of the order  

InvitationForm

Name Type Size Values Tooltip message Attributes
Who text 60   Who was invited  
Reply select 3 No,Accepted,Declined Have they replied?  

Creator Topics

Typically a TWiki application like this has topics that contain HTML forms tailored for creating new topics. For the purposes of this tutorial we're just going to assume they exist.

Simple Queries

We want to know:

  • Everything we need but have not yet ordered
    %SEARCH{"form.name='OrderForm' AND Status='Required'" type="query"}%
  • Everything we don't yet have for the party
    %SEARCH{"form.name='OrderForm' AND (Status='Required' OR Status='Ordered')" type="query"}%
  • Everyone who has confirmed they are coming to the party
    %SEARCH{"form.name='InvitationForm' AND Reply='Accepted'" type="query"}%
We don't actually need the form.name='...' in the queries, because our simple forms don't have any common fields - fields with the same name in both forms. However it's still a good idea to put it in, because it helps TWiki narrow down on the set of topics that have to be searched for the other terms in the query.

Getting a bit more sophisticated

Let's say we want to find out all the costumes we are hiring for the party, but we're renting them from a number of different suppliers - Marvel Costume Hire, Toga Costumiers and Costumes-r-Us. To do that we use the fact that they all have Costum as part of the Supplied field, so we can use a ~ (like) search.

%SEARCH{"form.name='OrderForm' AND Supplier~'*Costum*'"}%

The ~ operator supports a couple of different 'wild cards' in the string to be matched; * matches any number of characters, and ? matches any single character.

Now, every person we invite needs to have a picture attached to their invite topic, so the Master of Ceremonies can recongnise them and call out their name at the top of the stairs. Some pictures will be supplied by the invitees themselves, but other will have to be obtained from stock photographs (we did tell you this was a celebrity party, right?). We know which ones are from stock, because the image files all have stock_ at the start of the attachment name. We have to track these because, of course, all stock photographs are copyright and have to be paid for. We find all the invite topics that contain a stock photograph using an array search

%SEARCH{"form.name='InvitationForm' AND attachments[name~'stock_*']"}%

attachments is what we call an associative array - an array that can be indexed by another query. So attachments[name~'stock_*'] will find all elements of the attachments array that match the query name~'stock_*'. If there is at least one match, the whole expression will return a true value.

You can also find all invites that have two or more stock photos, by using the length operator:

%SEARCH{"form.name='InvitationForm' AND length(attachments[name~'stock_*']) > 2"}%=

And we can find all topics that do not have a stock photo attachment by simply inverting the query:

%SEARCH{"form.name='InvitationForm' AND NOT attachments[name~'stock_*']"}%

-- Contributors: CrawfordCurrie - 14 Jun 2007

Discussion

In your examples you have:

%SEARCH{"form.name="InvitationForm" AND NOT attachments[name~'stock_*']"}%

Don't the nested " around the form name (InvitationForm) screw up the parser since it looks like the entire query is encosed in "s. I note that you use ' for the value of the name match. Neither this topic nor QuerySearch defines the structure of the query as it relates to quotes, field values etc. I was realy expecting something like

%SEARCH{ type="query" query="what you have above w/ single quotes for tstrings with embedded spaces"}%

The examples in QuerySearch: %SEARCH{"parent.name = 'HowToWriteQueries'" web="Codev" type="query"}%

seem to follow my theoretical syntax, but the examples above don't.

-- JohnRouillard - 15 Jun 2007

Oops, you are right, I should have used ' - well spotted!

I wanted to avoid the use of a BNF for the syntax, and demonstrate it by example instead. Please feel free to hack this topic to clarify things - I'll soon point out if you get it wrong!

-- CrawfordCurrie - 15 Jun 2007

Edit | Attach | Watch | Print version | History: r3 < r2 < r1 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r3 - 2007-06-15 - CrawfordCurrie
 
  • 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.