Subject RE: [IBO] Distinct or not in FTS
Author IBO Support List
Post date 2013-10-12T19:41:45Z
Give this public property of TIB_BDataset (TIB_Query) a look and see if it does what you need:
 
{: This is a hack that will suppress duplicate records which are returned from
a query that is, for example, a self-join in a full text search using partial
word matching criteria.
<br><br>
In order for it to work, set the KeyLinks to the distinct column(s) so that it
will ignore any records fetched with that same bookmark.}
  property SuppressDuplicates: boolean read FSuppressDupRecs
                                       write FSuppressDupRecs;
I use this feature in the full text searching because under some circumstances you cannot get proper server optimization on some queries as a DISTINCT and so if you flip this to true you shift the burden of this over into the client application. It does a scan of all previously fetched bookmarks and if it sees that the record has already been fetched it simply ignores it rather than adding it into the dataset. Thus, you effectively get the behavior of DISTINCT in your query without imposing the DISTINCT operator on the server.
 
For obvious reasons, this property doesn't exist on the TIB_Cursor component. It isn't buffered and so there's no way to tell what has already been fetched.
 
Jason
 
 
From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] On Behalf Of danymarmur@...
Sent: Friday, October 11, 2013 8:29 AM
To: IBObjects@yahoogroups.com
Subject: [IBO] Distinct or not in FTS

I have been pulling my hair out (well, i'm bald but anyway) on this.

I have FTS set up and in the IB_FTS util *application* when i search i get one row for each hit regardless.

If the used column in the FTS$<<INDEX>>$WRD contains more than one entry for the table id, this can happen when using partial matches, i get one hit record back for each match - generation some duplicates.

Now i could put a "Distinct" keyword in my query and that would work, but the IB_FTS application does not! So i've been looking at where the duplicates are removed. I can not find the place!

This is very frustrating. If i look at the IB_FTS application SQL monitor output firebird does give back the duplicates. And if i copy the query from the monitor output and run it in a DB management application i'll get the duplicates too.

It was some time since i used the "native" IBO components so maybe there's an automatic keylink-duplicate removal thingy somewhere? Please, if anyone knows could you point me to the property/code?

This may sound as a picky question but i need to get the searches absolutely right for a high-load server application.

TIA,

/Dany