QueryUniqueValues < Development

You are here: Foswiki>Development Web>QueryUniqueValues (revision 12) (raw view)

---+!! %TOPIC%

%TOC%

---++ Query for unique values

It'd be brilliant to query for unique values. The new stores - DBIStoreContrib (SQL =DISTINCT= clause) and MongoDBPlugin (via [[http://www.mongodb.org/display/DOCS/Aggregation#Aggregation-Distinct][distinct()]] or some map/reduce collection) could provide this functionality quite cheaply.

Introduce a 'distinct' function. Example below builds on the idea of a 'topics' collection, see QueryCustomCollections, so that System.VarQUERY isn't tied to one-topic-at-a-time

---+++ Get distinct list of authors for all topics beginning with Foo
Sven's syntax suggestion: %JQREQUIRE{"chili"}%<verbatim class="tml">%QUERY{"distinct(topics[name~'Foo*'].author)"}%</verbatim>

This doesn't allow for reporting of distinct _combinations_ of more than one key; OTOH, System.VarQUERY can only emit a flat array of values for one field at a time... For example we can't do this: <verbatim class="tml">%QUERY{"topics[name~'Foo*'].distinct(author, Project)"}%</verbatim>

When would you use this in a SEARCH... Maybe with IN. This might search for extensions that have never had a bug filed against them (flaw: assumes Component holds only one value):
<verbatim class="tml">%SEARCH{
  "NOT (name IN (distinct(topics[form.name='ItemTemplate'].Component)))"
  type="query"
}%</verbatim>

.. probably a bad example.

I want to apply the distinct() operator on some meta key across a set of topics... but I'm not sure how to construct that in the querysearch language.

-- Main.PaulHarvey - 26 Oct 2010
---++ Discussion

I prefer the functional version of this:

<verbatim class="tml">%QUERY{"distinct(topics[name~'Foo*'].author)"}%</verbatim>

-- Main.SvenDowideit - 26 Oct 2010

Cool. I updated the brainstorm (?)

-- Main.PaulHarvey - 29 Oct 2010

The functional version is more in line with the rest of the query language (d2n etc). My main question (and the essential starting point for any more work) is, how would you implement this in =OP_distinct=?

-- Main.CrawfordCurrie - 29 Oct 2010

you mean the brute force =OP_distinct::evaluate()=? It would require a state - a list of values previously found. And that would be depressingly non-paralellisable.

mind you, I'm poking out of order execution, so it could be worse.
 
-- Main.SvenDowideit - 29 Oct 2010

Yes, I mean exactly that. However it doesn't require a state; it requires an array, which is the result of the inner evaluate. It should then uniq that set. I think. Forcing ourselves to always implement an =OP_= version helps stop us from getting carried away.

-- Main.CrawfordCurrie - 29 Oct 2010

dammit. would you please stop reaching for an array when a hash is more appropriate? Same issue in the Logger API - I'm going to have to amend that to use hashes too :(

:p

-- Main.SvenDowideit - 30 Oct 2010

Sorry <del>array</del><ins>set</ins>

-- Main.CrawfordCurrie - 30 Oct 2010

The main use-case is building facted search UIs; allowing users to "drill down" just by clicking on (automatically discovered) refinement options.

-- Main.PaulHarvey - 10 Mar 2011

It might be worth looking at  SolrPlugin and DBCachePlugin how they both implement this feature. For Solr it is a natural thing to compute facets along with a result as that's what this
search engine has been invented for mainly.
In DBCachePlugin there's the DBSTATS macro that allows to analyze certain properties of a query provided, like frequencies of individual values, their min, max, and mean values. Solr, of course allows for a lot more than that using function facets that are computed dynamically. Values returned are then available for further refinements of a subsequent query. 

-- Main.MichaelDaum - 01 May 2011

BasicForm edit

TopicClassification	BrainStorming
TopicSummary	Provide a way to have a query return only unique values
InterestedParties	PaulHarvey

Topic revision: r12 - 01 May 2011, MichaelDaum

Development

Quick Links

Tools

The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. see CopyrightStatement.