Question
· Jan 2, 2021

SQL Search/iFind to find out how many matches are there

I have an SQL text index defined like this

Index TextIndex On (Text) As %iFind.Index.Basic(IGNOREPUNCTUATION = 1, INDEXOPTION = 0, LANGUAGE = "en", LOWER = 1);

If I run a query like this:

SELECT
Text
FROM Post
WHERE %ID %FIND search_index("TextIndex",'ABC')

I get 20 results with posts containing the term ABC.

But each post can contain the term ABC several times.

Is there a way to get a total count of ABC term?

Is there a way to get a count of ABC term in each Post?

Product version: IRIS 2020.4
Discussion (13)1
Log in or sign up to continue

That won't return accurate count unfortunately.

Consider a Text value like: ABCD ABC. It would be returned by iFind since ABC is in this text, however $Find would return 2 - incorrect number of ABC instances. More advanced iFind variants do additional text/term processing so I'd like to use it.

Additionally I know it's possible as there is highlight function so iFind can determine a number of search term occurrences in a string and highlight them, but I only need to return the number of occurrences.

I can highlight and count <b>,  but I wonder if a better solution exists.

You can expose this information through setting the IFINDMAPPINGS parameter to 1:

  • [class_name]_[index_name]_WordRec: stores which words appear in each record in this index. See also %iFind.Index.AbstractWordRec.
  • [class_name]_[index_name]_WordSpread: stores the total number of records in which this word appears in this index. See also %iFind.Index.AbstractWordSpread.
  • [class_name]_[index_name]_WordPos stores which word occurs at which position in a record, so it can be joined to the AttributePos table. See also

So by doing a COUNT() on the WordPos table, you should find what you're looking for IFF it corresponds to an actual word. If you're using wildcards, you might combine with %iFind.FindWords() as a TVF, but that'd still be looking for individual words only:

SELECT COUNT(*) 
FROM Test_IFind.IFind_IF_WordPos 
WHERE WordId IN (SELECT WordId FROM %iFind.FindWords('ab*'))

If you want to count any kind of match, your highlight trick is probably the nicest way to get at it.

(as an aside: with the introduction of the %iFind.Index.Minimal index type in 2020.1, which BTW was Eduard's suggestion ;-), it seems the class reference for the IFINDMAPPINGS projections added by the .Basic class but not in .Minimal got lost. We'll fix that shortly!)

Thank you!

That's exactly what I need. I knew about iKnow relational mappings, but not iFind ones.

If you want to count any kind of match, your highlight trick is probably the nicest way to get at it.

The problem with this approach is:

  • It actually reindexes the source again which takes time
  • It does not seem to be aware of the index so index parameters would be missed as only INDEXOPTION can be passed.