Is it possible to embed a verity search within a database search?

[alabamaarchives]

I need to be able to use a keyword entered by a user to do a full text search of a collection and then limit the outpout by parameters in a database.

[Carl Von Stetten]

Can you elaborate a little bit on what you are trying to do?  It's not entirely clear from your post.  Please be specific, maybe even provide an example.

[alabamaarchives]

We are doing a search of some newspapers in .pdf format.  We need to do a full text search for any word or phrase entered by the user.  We need to take the table created by the keyword search and be able to limit the output by paper name, county etc.  So if I entered "baseball" in the keyword search but I only wanted the term when found in the New York Times on July 4, 2013.  The newspaper pdf, name, county, date, etc are fields in a database.

[Carl Von Stetten]

OK, makes sense. Does the Verity collection store some kind of document identifier that matches with an identifier in your database?

One way to skin this is with two ColdFusion query objects.  One query will be the results of your <cfsearch> on the Verity collection (as an aside, I don't know what version of ColdFusion you are on, but I'd plan on having to migrate that collection to SOLR at some point).  The other query will be the database query.  You can then do a query-of-query and join the two original queries together, assuming again that there is some common identifier in your database.  There are some problems with this approach, though.  Depending on how large your collection is and the number of matches to your keywords, you may not be able to fit all this in memory.

With SOLR (whether using the version built into ColdFusion 9+ with <cfindex>/<cfsearch>) or a standalone version using <cfhttp> calls, you might be able to store the database fields in the actual collection, as SOLR supports a whole bunch of custom collection attributes.  You'd have to work out the details of how to wire that up and maintain it, but then your collection search could filter the keywords and the other criteria all at once.

I hope this gives you some useful ideas.

-Carl V.