DBQUERY with variable search argument

Discussion

Is there a way to construct DBQUERY search arguments dynamically so that a query could be refined? I used to do this with the FormQueryPlugin, which could construct successive queries by taking previous queries as input. This produced something like the effect of creating temporary tables which could themselves be queried.

I upgraded an installation from 1.1.4 to 1.1.9 recently and it seems that the FormQueryPlugin finally stopped working with the newer DBCacheContrib. I read elsewhere that formal support for FormQueryPlugin has ended. The suggestion was to use the DBCachePlugin to run queries.

With that in mind, I thought I would create a refined search with some IF macros with nested calls to SESSION_VARIABLE gradually adding on more search terms to a query string based on user input.

Unfortunately, it seems that I can't get DBQUERY itself to accept a variable argument (via a SESSION_VARIABLE call in place of the usual "search" string). The query exits with an error, printing the offending query that doesn't work - which appears exactly the way I want and DOES work if I literally type the DBQUERY with a regular string constant as argument.

I imagine there must be some meta / extra character information attached to the SESSION_VARIABLE macro expansion (or within my constructed string) that is messing up the DBQUERY. The SESSION_VARIABLE contents render like I expect to the screen, but then I have no idea what meta/escape characters might have been removed in the rendering process.

Can DBQUERY simply not handle a MACRO in its search argument?

Thanks for suggestions.

-- JohnRoberts - 17 Apr 2015

Hi John, how about you add your experimental wiki app in here to illustrate in terms of CODE what you are trying to do.

-- MichaelDaum - 17 Apr 2015

Hi Michael, I'm not actually writing an app per se, but filling a Wiki page with DBQUERY results. Originally, I used FORMQUERY chains. See the FORMQUERY example below. I don't want to revive the FORMQUERY plugin, but would like to duplicate such chaining with DBQUERY.

Now to duplicate the chaining effect that FORMQUERY provided, but using DBQUERY, two possibilities occur to me:

1. Constructing and succesively refining the search string fed to DBQUERY.
   • The refinements would be added to the search string and then the string itself fed to a DBQUERY once.
   • I have attempted this approach and have failed. DBQUERY doesn't seem able to take a variable argument.
   • I will upload some examples.
2. Capturing DBQUERY output topic list and then feeding that to the next DBQUERY as the include list of topics.
   • I have not yet tested this approach.
   • Similar to taking a variable search string, the include option would need to be able to accept a variable argument.

Update: A single SESSION_VARIABLE substitution is working for the search string. I'm still having problems with a more complicated search. Let me put this on hold until I can figure out my trouble with even this simple example.

I modified the test and things seem to be working now for this simple example. I think one issue I was having problems with was how to address the form variables in the DBQUERY search: whether to use the map X.Y format or to use just the Y field. It seems I should use the Y field.

Using X.Y mapping syntax in my DBQUERY may have been the source of my earlier query failures rather than using a variable argument.

I'm going to take a look at my more complicated search page and see if I can correct the query problems that first prompted this question.

-- JohnRoberts - 17 Apr 2015

Commenting is disabled while not logged in

FormQuery Comparison

Here's an example of how the FORMQUERY chaining used to work. This is a bit of wiki to run on the System web that searches for topics with the FAQForm. It then refines the search to select only those with "how" in the summary.

Wiki Text Of Test

---++ !FormQuery
---+++ Original Search
<verbatim>
%FORMQUERY{name=QueryFAQ search="(form.name='FAQForm') AND (topic != 'FAQForm') AND (topic != 'FAQTemplate')"}%
%SHOWQUERY{query=QueryFAQ header="|  *Topic*  |  *Summary*  |" format="|$topic|$TopicSummary|"}%
</verbatim>
Result: 
%FORMQUERY{name=QueryFAQ search="(form.name='FAQForm') AND (topic != 'FAQForm') AND (topic != 'FAQTemplate')"}%
%SHOWQUERY{query=QueryFAQ header="|  *Topic*  |  *Summary*  |" format="|$topic|$TopicSummary|"}%

---+++ Refine Search
Feed the original search back into itself to create a refined search.  This is the useful part.  Rather than hard coding the 'how' as in this example, the !TopicSummary could be matched against a string controlled by a URL parameter from an html form.  Or, you might imagine surrounding these FORMQUERY query refinements in IF macros to control what refinements were performed.
<verbatim>
%FORMQUERY{name=QueryFAQ query=QueryFAQ search="lc(TopicSummary) =~ 'how'"}%
%SHOWQUERY{query=QueryFAQ header="|  *Topic*  |  *Summary*  |" format="|$topic|$TopicSummary|"}%
</verbatim>
Result: 
%FORMQUERY{name=QueryFAQ query=QueryFAQ search="lc(TopicSummary) =~ 'how'"}%
%SHOWQUERY{query=QueryFAQ header="|  *Topic*  |  *Summary*  |" format="|$topic|$TopicSummary|"}%

Give me a minute and I'll post the results as seen on a 1.1.4 and 1.1.9 foswiki.

On a 1.1.4 Server

• Form query search as implemented on a 1.1.4 foswiki.:
  
• Refined form query search on 1.1.4. Query is fed back to itself with further search terms.:
  

On a 1.1.9 Server

• Form query search on foswiki 1.1.9. Error seems to be more an issue of showquery not expanding variables, because the number of rows indicates that some results were found.:
  
• Refining the search on a foswiki 1.1.9 generates errors.:
  

DBQUERY

SESSION_VARIABLE Argument

Example Wiki Text

---+++ Simple Variable Argument
---++++ Hardcoded DBQUERY
---+++++ Original
%DBQUERY{"(form.name =~ 'FAQForm')" exclude="FAQ(Form|Template)" hidenull="on" format="|$topic|$formfield(TopicSummary)|"}%
---+++++ Refined
%DBQUERY{"(form.name =~ 'FAQForm') AND (lc (TopicSummary) =~ 'how')" exclude="FAQ(Form|Template)" hidenull="on" format="|$topic|$formfield(TopicSummary)|"}%
---++++ DBQUERY With Simple SESSION_VARIABLE Input
%SESSION_VARIABLE{"MyDBQUERY" set="(form.name =~ 'FAQForm')"}%

%SESSION_VARIABLE{"MyDBQUERY"}% 

%DBQUERY{"%SESSION_VARIABLE{"MyDBQUERY"}%" exclude="FAQ(Form|Template)" hidenull="on" format="|$topic|$formfield(TopicSummary)|"}%

---++++ DBQUERY With Concatenated SESSION_VARIABLE Input
%SESSION_VARIABLE{"MyDBQUERY" set="(form.name =~ 'FAQForm')"}%

%SESSION_VARIABLE{"MyDBQUERY" set="%SESSION_VARIABLE{"MyDBQUERY"}% AND (lc(TopicSummary) =~ 'how')"}%

%SESSION_VARIABLE{"MyDBQUERY"}% 

%DBQUERY{"%SESSION_VARIABLE{"MyDBQUERY"}%" exclude="FAQ(Form|Template)" hidenull="on" format="|$topic|$formfield(TopicSummary)|"}%

---++++ DBQUERY With Concatenated SESSION_VARIABLE Input, NOOP Variation
%SESSION_VARIABLE{"MyDBQUERY" set="(form.name =~ 'FAQForm')"}%

%SESSION_VARIABLE{"MyDBQUERY" set="%SESSION_VARIABLE{"MyDBQUERY"}% AND (lc(%NOP%TopicSummary) =~ 'how')"}%

%SESSION_VARIABLE{"MyDBQUERY"}% 

%DBQUERY{"%SESSION_VARIABLE{"MyDBQUERY"}%" exclude="FAQ(Form|Template)" hidenull="on" format="|$topic|$formfield(TopicSummary)|"}%

Result on Foswiki 1.1.9

At the moment, the very simple variable substitution is working. However, if I refine the search, it fails. I need to test this and come back with results.

I've tested it and now variable substitution with concatenated SESSION_VARIABLE usage is working.

Providing for Search Refinement Using URL Parameters

John - Are you wishing to retain the search results past viewing an individual search page? That's the only reason I can see for using session variables. There are several ways to pass the results from an initial search to subsequent searches within the same page. Perhaps I'm missing something regarding what you're trying to accomplish, however it possible to create search forms which allow users to refine the search simply by taking the url params from the initial search and feed them back into the search form itself.

Here's what it would look like using FAQ topics as example:

A few additional notes:

• If it was installed, I would use FlexFormPlugin to grab the form values and render the input elements dynamically.
• I didn't bother coding for the textboxlist form elements. FlexFormPlugin would take care of that, or else once could manually create those elements but would involve invoking the jquery plugins and I didn't want to bother with that for this example.
• I used QuerySearch since DBCachePlugin isn't installed. The syntax is similar but not exactly the same.
• The key part of how this work is 1) putting the url params into the search form element value attributes (so they are redisplayed in the form), and 2) putting a series of IfStatements in the search string attribute to include those extra clauses only if the asssociated VarURLPARAM is defined.
• I got a little carried away with providing extra feedback in the search results regarding Subject and Extensions. That's what all the nested IfStatements in the "format" attribute are about. Hope they don't distract from the basic concept being illustrated...
• Now if you wanted the 1st search form to remain as it was, but add additional form elements you could either:
  • Hide the secondary form elements until the "showrpt" urlparam is defined (similar to how I hid the search results above until there was something to show).
  • Add a separate secondary search form and results and use VarQUERYPARAMS to pass the set of search parameters from the initial search to the secondary search.
• I'll mention one other "trick" that I some times use for taking the results of one search and then slicing and dicing it in different ways in a page without having to repeat the initial search each time. This achieves something similar to FormQueryPlugin's 'result sets' and involves using VarINCLUDE to define a parameter that is a hash of the initial search results. Rather than trying to illustrate this technique here, I'd point you to SupportQuestions where I used this technique. Take a look at the raw code and you'll see an INCLUDE near the top of the page that looks like this:

  %INCLUDE{"%WEB%.%TOPIC%" 
       section="subjects" 
       QUESTIONLIST="%INCLUDE{"%WEB%.%TOPIC%" section="grabQuestions"}%"
  }%

  Because the QUESTIONLIST is defined by an include (rather than the search itself), the results are passed along as a static string that can be processed further.

Hope this gives you some ideas for different ways to approach your problem. -- LynnwoodBrown - 18 Apr 2015