Foswiki on GitHub is open for business! Next release meeting: Monday September 29, 1300Z

Feature Proposal: A query cookbook is essential to help break people in

Motivation

The query language used by Foswiki %IF, %SEARCH and %QUERY is rich, expressive and ..... complicated.

We need a strongly user oriented cookbook to help people use it.

I propose that this topic be used to collect examples for such a cookbook. Please contribute! Note that future features are marked
like this

so you know what's there in current releases (1.1) and what's only available in trunk.

What's in a topic

You can use the following names to refer to the different parts of the topic.
  • name - name of the topic
  • web - name of the web the topic is within
  • text - the body text of the topic (without embedded meta-data)
  • META:FILEATTACHMENT
    • for each attachment
      • name
      • attr
      • path
      • size
      • user
      • rev
      • date
      • comment
  • META:TOPICPARENT
    • name
  • META:TOPICINFO
    • author
    • date
    • format
    • version - topic version (integer)
  • META:TOPICMOVED
    • by
    • date
    • from
    • to
  • META:FORM- the main form of the topic
    • name
  • META:FIELD- the fields in the form.
    • for each field in the form
      • name - name of the field
      • title - title of the field
      • value - what is stored in the field
      • form - name of the form the field is in (currently always equal to META:FORM.name)
  • META:PREFERENCE
    • for each preference in the topic
      • name
      • value

By default all queries are performed on the latest version of a topic. If you need to access older versions of the topic, then you can use the versionsfield to do so:
  • versions
    • for each older version (most recent first, including the latest)
      • repeat of all the above fields.

Simple Tasks

Search for field values using AND and OR

Search where the form name is 'PersonForm', 'Job' field is 'Politician' or 'Lawyer', and the 'Character' field is one of 'Honest', 'Trustworthy' or 'Reliable'
%SEARCH{"form.name ~ '*PersonForm' AND (Job = 'Politician' OR Job = 'Lawyer') AND Character IN ('Honest', 'Trustworthy', 'Reliable')" type="query"}%

Note the use of ~ '*PersonForm' to match the form name. We use this construction because a form name can be stored as a simple topic name (where the form is in the same web) or as a Web.TopicName (where the form is in a different web).

Test if somebody is a direct member of a group

Option 1:(Foswiki 1.1.x)
%IF{"'%GROUPINFO{"MaintainGroup" expand="0"}%'=~'\bSvenDowideit\b'" then="yes" else="no"}%

Test: yes

Option 2:(Foswiki 1.2+)
%IF{"'SvenDowideit' IN (%GROUPINFO{"NewUIGroup" format="\\'$username\\'" separator=", " expand="0"}%)" then="yes" else="no"}%

Test: IF{ "'SvenDowideit' IN ()" else="no" then="yes" }: Missing operator in ''SvenDowideit' IN ()'

List the titles of all form-fields that have the value 'Yes'

%QUERY{"fields[value = 'Yes'].title"}%

Search in a referenced topic

Let's say our 'PersonForm' has a field 'Skills', which is designed to accept the name of a differenttopic, which lists the skills of the individual in the topic text. We want to search for all people who list 'Taxidermy' as one of their skills.
%SEARCH{"form.name ~ '*PersonForm' AND Skills/[text =~ /Taxidermy/]" type="query"}%

Search for topics in certain states

Search for topics where the "State" field is set to "Complete" or "Rejected" and the 'Customer' field is one of "IBM" or "Sony"
%SEARCH{"State IN ('Complete','Rejected') AND Customer IN ('IBM', 'Sony')" type="query"}%
(This can also be done using OR, but it's easier to deal with lists)

Get the attachment count of the current topic

%QUERY{"length(attachments)"}%

Get the attachment count of shown vs hidden topics

No. Attachments %QUERY{"length('System.FamFamFamContrib'/length(attachments[NOT lc(attr)=~'h'])"}%/%QUERY{"length('System.FamFamFamContrib'/attachments[lc(attr)=~'h'])"}% (shown/hidden)

No. Attachments 0/6 (shown/hidden)
HELP lc(attr)=~'h' could be written as the simpler attr='h', but the attr value in a META:FILEATTACHMENTrecord may contain more than one attribute, and perhaps not all (legacy) applications are known to use lower-case 'h'.
  • lc() : lower-case
  • =~ : 'contains' (regular expression) operator

Search for topics with both shown AND hidden attachments, display counts

%SEARCH{
  "length(attachments[lc(attr)=~'h']) > 0 AND length(attachments[NOT lc(attr)=~'h']) > 0"
  type="query"
  web="Extensions"
  limit="3"
  header="---++ [[$web.%HOMETOPIC%][$web]]
| *Topic* | *Shown* | *Hidden* |"
  format="| [[$web.$topic][$topic]] | $percntQUERY{\"length('$web.$topic'/attachments[lc(attr)=~'h'])\"}$percnt | $percntQUERY{\"length('$web.$topic'/attachments[NOT lc(attr)=~'h'])\"}$percnt |"
}%

Searched: length(attachments[lc(attr)=~'h']) > 0 AND length(attachments[NOT lc(attr)=~'h']) > 0

Extensions

Topic Shown Hidden
ActionTrackerPlugin 4 5
AliasPlugin 1 5
AutoCompletePlugin 1 5
Number of topics: 3

Display all non-hidden *.png attachments

%SEARCH{
  "attachments[name~'*.png' AND NOT lc(attr)=~'h']"
  type="query"
  web="Extensions"
  limit="3"
  header="---++ [[$web.%HOMETOPIC%][$web]]"
  format="   * [[$web.$topic][$topic]]
$percntFORMAT{
   \"$percntQUERY{'$web.$topic'/attachments[name~'*.png' AND NOT lc(attr)=~'h'].name}$percnt\"
   type=\"string\"
   format=\"      * [[%PUBURLPATH%/$web/$topic/$dollaritem][$dollaritem]]\"
}$percnt"
}%

Searched: attachments[name~'*.png' AND NOT lc(attr)=~'h']

Extensions

Number of topics: 3

Working with Dates

Report a topic as "unloved" if it was last edited before 1st Jan 2010

Topic is %IF{"info.date < d2n('1 Jan 2010')" then="unloved" else="loved"}%

Example: Topic is loved

Generate a NEW if the current topic was last edited less than a week ago

%%IF{"info.date > now - 7*24*60*60" then="N" else="NOP"}%%
Example: NOP%

Get the version of a topic at a given date

Get the latest version of 'Development.QueryAcrossTopicRevisions' as of 1st Dec 2010
%QUERY{"('Development.QueryAcrossTopicRevisions'/versions[info.date < d2n('1 Dec 2010')])[0].info.version"}%
Example:

Working with Versions

Find the version numbers of all revisions that were authored by a user

%QUERY{"versions[info.author='SamuelJohnson'].info.version"}%

Find the authors of all revisions

... of 'MyBiography' that had the field 'State' set to 'Authorised'
%QUERY{"'MyBiography'/versions[state='Authorised'].info.author"}%

Find all topics that were created by GalileoGalilei

%SEARCH{"versions[-1].info.author='GalileoGalilei'" type="query"}%

Get the authors of the last 3 versions of the current topic

With the most recent last, as a JSON array:
%QUERY{"versions[2,1,0],info.author" style="json"}%

Find all contributors for a topic

%QUERY{"versions.info.author"}%

isn't good enough. We need a uniq operator

Technical Resources

(Not intended for inclusion in the final cookbook; here just as a reference for authors).

Query Language BNF. BNF is for trunk, current release does not have comma or arithmetic operators.

expr       ::= and_expr 'or' expr | and_expr;
and_expr   ::= not_expr 'and' and_expr | not_expr;
not_expr   ::= 'not' comma_expr | comma_expr;
comma_expr ::= cmp_expr ',' comma_expr | cmp_expr;
cmp_expr   ::= add_expr cmp_op cm_expr | add_expr;
cmp_op     ::= '<=' | '>=' | '<' | '>' | '=' | '=~' | '~' | '!=' | 'in';
add_expr   ::= mul_expr add_op add_expr | mul_expr;
mul_expr   ::= ref_expr mul_op mul_expr | ref_expr;
mul_op     ::= '*' | 'div';
ref_expr   ::= u_expr ref_op ref_expr | u_expr;
ref_op     ::= '/' | '.';
u_expr     ::= value uop u_expr | value;
uop        ::= 'lc' | 'uc' | 'd2n' | 'length' | '-' | 'int';
value      ::= name | string | number;
number     ::= unsigned floating point number, 'C' syntax
string     ::= single-quoted constant string

Operators are not case sensitive. However field names are. Note that the comma operator only exists on trunk.

-- Contributors: CrawfordCurrie - 10 Dec 2010 ArthurClemens, PaulHarvey

Discussion

Normally we do not need feature proposals for writing documentation. It is good to discuss and collaborate on it. But we do not really need a decision for or against adding or improving documentation. Feature proposals are mainly to discuss

  • Changes to how the software behaves to the end user where features are added or removed
  • Changes to the markup language
  • Changes to the syntax of the macros in core and default plugins
  • Changes to the API used by extensions
  • Changes to the storage formats that affects how e.g. searches work

Things that do not need feature proposals

  • Code refactorings where the functions remain the same
  • Bug fixes
  • Documentation improvements and enhancements
  • Any changes to none default plugins

Things that are not feature proposals but where the feature proposal process is a good existing and known way can be
  • Political and marketing decisions where a proposer seeks a community decision
  • Changes to foswiki.org website where it is nice to get a broad consensus before changing significant setups or design

So what I am saying is - let is write this cookbook. Anyone against I change this from proposal to brainstorm?

-- KennethLavrsen - 10 Dec 2010

I wrote a javascript query builder for work recently and wanted to create a Contrib out of it but became too time-challenged. I still intend to do it but, in the meantime, here is the core of it for inspection/inspiration. It requires the FlexFormPlugin and RenderPlugin and presently only builds queries from form elements (no meta data). I'm using foswiki.HijaxPlugin.serverAction (from HijaxPlugin) for the ajax calls but that can be replaced with jQuery.ajax() if prefered. The QueryBuilder page is attached. The following (lifted directly from the app at work) shows how it can be used.
%INCLUDE{"%SYSTEMWEB%.QueryBuilder" FORM="RnT.ProposalForm" EXTRA="exclude=\"TopicType|SubjectArea|WorkflowStatus\""}%

<div class="hidden">
%STARTSECTION{name="querysearch"}%
<input type="submit" class="foswikiSubmit" id="startEdit" value="Edit and lock the selected topics" />
<input type="submit" class="foswikiButton" id="deselectAllTopics" value="Clear all" /> 
<input type="submit" class="foswikiButton" id="selectAllTopics" value="Select all" />
| *Edit* | *Proposal* | *Program* | *Technology Domain* | *Author* | *Associated Call* |
%%URLPARAM{trigger}%{"
TopicType = 'ProposalTopic' AND %URLPARAM{querystr}%
" type="query" web="%BASEWEB%" excludetopic="*Template" format="| \
<input type='checkbox' name='topiclist' value='$topic' class='topicTitles' checked /> | \
[[$topic]] %BR% $percntFORMFIELD{$quotTitle$quot topic=$quot$web.$topic$quot}$percnt | \
$percntFORMFIELD{$quotProgram$quot topic=$quot$web.$topic$quot}$percnt | \
$percntFORMFIELD{$quotTechnologyDomain$quot topic=$quot$web.$topic$quot}$percnt | \
$percntFORMFIELD{$quotAuthor$quot topic=$quot$web.$topic$quot}$percnt | \
$percntFORMFIELD{$quotAssociatedCall$quot topic=$quot$web.$topic$quot}$percnt |"
nonoise="on" order="created" reverse="on"}%
%ENDSECTION{name="querysearch"}%
</div>

-- DavidPatterson - 10 Dec 2010

I made this a feature request to keep it on the radar. Brainstorming topics tend to get drowned.

-- CrawfordCurrie - 11 Dec 2010

There might be some relevant snippets in Support/Faq40

-- PaulHarvey - 15 Jan 2011
Topic revision: r15 - 23 Apr 2013, ThomasSchmidt
 
The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. see CopyrightStatement. Creative Commons License