Face search drill-down
With Scimore you can do facet search. The primary function of facet search, is to assist users to drill into a search result, by helping them select additional search criterias.
It allows do drill down in the result of a sql statement, both free text search statements and traditional sql queries.
It is done using the special sql command rotate. This turns columns into rows. The easiest way to understand it, is to see it in action.
First we get the first results froma a free text search on database of DVD's for clint eastwood
select top 2 * from
execute freetext(
select score, title, category, language, rating, actors, directors from dvds_text
where ##query = 'Directors:(clint eastwood)')
This is a standard sql query against a scimoredb freetext index. And it gives:
Now we use the rotate command.
select rotate category, rating, language, Actors, directors from (
select top 2 * from
execute freetext(
select score, title, category, language, rating,
actors, directors from dvds_text
where ##query = 'Directors:(clint eastwood)'))
With the rotate command the result set looks very different. It now consists of the columns #name, and #value.
The result is that each cell becomes a row, containing the name of the column and the value of the column. Later using you will see how this can be used to analyse the output from sql commands.
We can see that one actor cell contains more than one actor. The cell can be splittet using the split command, which works in co-operation with the rotate command.
select rotate category, rating, language, split(actors,'|') as Actors, directors from (
select top 2 * from
execute freetext(
select score, title, category, language, rating,
actors, directors from dvds_text
where ##query = 'Directors:(clint eastwood)'))
Let now start analysing the result. We remove the top criteria, and get the full result set. Then we add a GROUP BY, to count the number of occurances, and finally restrict the number of results by adding a HAVING clause.
select #name, #value, count(*) as [count] from (
select rotate category, rating, language, split(actors,'|') as Actors, directors from (
execute freetext(
select score, title, category, language, rating,
actors, directors from dvds_text
where ##query = 'Directors:(clint eastwood)')))
group by #name, #value
having count(*) > 1
order by #name, count(*) desc
So the DVD's produced directed by Clint Eastwood, we get an overview of which actors are used how frequent, the type of movie, the language and the ratings.
Limitations
It's a fairly heavy operation to group over a large result set, thus when rotate returns a large recordset, these types of operations can be heavy. Its not recommended to rotate and group command on results larger than 5000 rows, depending on the requirements of speed, the size of the rows, and the speed of the machine.