query-based Verity collection with Ctegories?

1) does a database query-based Verity collection support Categories? or is it supported only for document/directory based collections? 2) i am creating a collection and search interface for a multi-faceted photo database, where each photo has multiple facets, categories and keywords assigned to it. while i can create a collection that combines all the facets/categories/keywords in the collection's body (together with title and description), i think it will work better with facets and categories stored as collection/photo categories... i am using custom fields to store photo properties, photographer name and pre-set galleries a photo is assigned to, which takes up all 4 allowed custom fields... maybe someone has done something like this before and can share the insight?... here's the collection index code: <cfindex     action="refresh"     collection="photodb_simplesearch"     type="custom"     query="imgdata"     key="image_id"     title="image_file"     body="title,description,facets,cats,keywords" custom1="orientation" custom2="colormodel" custom3="galleries" custom4="owner_name"> the facets, cats, keywords and galleries are comma-delimited lists of each photo's facets, cats, keywords, galleries and their respective descriptions [created with mysql's group_concat() function - gotta love that thing!] for the prupose of being searchable for user-entered search phrase. any thoughts/suggestions? tia, -- Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/

i guess nobody uses Verity? :) Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/

I use Verity often (including in an education-oriented document management project that I'm working on), but have avoided categories...probably because of the lack of good documentation on their use. What I do is index documents with Verity, and have the [key] be the PK in a documents table in my database.  I will then create any other tables that I need to further describe or categorize the data and use that to "refine" my verity search by excluding records as needed with an in/not in statement in my SQL.  That's a very, very boiled down description of the approach I take, there does tend to be a lot more going on... Pete

I second that. I have just done something similar although I found that when lots of results were returned by the verity search, something like this far out performed using an 'IN (keyList)' statement (even when the second query returned a huge number of results): <cfsearch collection="theCollection" criteria="#search#" type="simple" name= "qVerityResults"> <cfquery name="qDBFilterResults" datasource="#theDatasource#"> *SELECT* * *FROM* tableX *WHERE* filters = #theFilters# <!--- yes, cfqueryparam - could be many filters here ---> </cfquery> <cfquery name="qFinalResultSet" dbtype="query" maxrows="#maxRows#"> *SELECT* qVerityResults.*, qDBFilterResults.* *FROM* qVerityResults, qDBFilterResults *WHERE* qVerityResults.*key* = qDBFilterResults.tableXId </cfquery> HTH Dominic ----- Excess quoted text cut - see Original Post for more -----

Dominic, thanks for your tip. It looks interesting... So you basically perform a full-text search using Verity, and search for other 'filters' using db, and then filter the db-based search to return only results matched by Verity search, avoiding the IN () comparison. Did I get that right? That looks like the solution for me, as I will have to return a lot more data about each photo that a Verity collection can index... I have tried using Categories in a collection, and though categories are supported for db query-based collection, they do not work the way I though they would... Short of creating a separate collection for each possible facet-category-keyword combination, I can't achieve what I wanted with them...  I thought having my b query group the records by, say, facet, and then having all facets listed as categories in the collection would 'sort' the collection by facets, but instead all the photos were assigned all the categories in the collection... If your approach really outperforms the basic recommended IN () filtering, that's the way forward i guess! Thanks for your input! Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Dominic Watson wrote: ----- Excess quoted text cut - see Original Post for more -----

hi Dominic, for some reason your approach with filtering verity results with a qoq gives me an error: Query Of Queries syntax error. Encountered "vSearch key. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition, my qoq is: <cfquery name="qoqSearchResults" dbtype="query"> SELECT * FROM vSearch, dbSearch WHERE vSearch.key = dbSearch.image_id </cfquery> if i switch the columns in the WHERE clause around (WHERE dbSearch.image_id = vSearch.key), i get error: Query Of Queries syntax error. Encountered "KEY. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition, Unrecognized character in operand, the qoq syntax is fine, as it works fine with any other qoq... is it due to one query being Verity resultset? any ideas? Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Dominic Watson wrote: ----- Excess quoted text cut - see Original Post for more -----

> for some reason your approach with filtering verity results with a qoq > gives me an error: Oh yeh, you must put it in square brackets as CF sees it as a keyword even thought it is prefixed. Forgot about that. <cfquery name="qoqSearchResults" dbtype="query"> SELECT * FROM vSearch, dbSearch WHERE vSearch.[key] = dbSearch.image_id </cfquery> Dom -- Blog it up: http://fusion.dominicwatson.co.uk

Thanks, Dominic, However... the qoq started complaining about comparing integers to strings... I guess Verity search returns all data as strings... I had to CAST image_id column in db query as CHAR, and it started working... But... it that right? Should it be like that? I am worried that casting a column as char, and then comparing string values in the qoq might actually bring in a big overhead and make it slower than doing an IN () comparisson... Any thoughts? Thanks, Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Dominic Watson wrote: ----- Excess quoted text cut - see Original Post for more -----

> However... the qoq started complaining about comparing integers to > strings... I guess Verity search returns all data as strings... > I had to CAST image_id column in db query as CHAR, and it started > working... But... it that right? Should it be like that? > I am worried that casting a column as char, and then comparing string > values in the qoq might actually bring in a big overhead and make it > slower than doing an IN () comparisson... Any thoughts? That's odd, I've not had that problem, damn QoQ! Also, if you are wanting to maintain the order of the verity results the QoQ may still be neccessary / useful. Doing a DB select with the IN() clause will ignore the verity order. Sorry I can't help with the casting. Dominic -- Blog it up: http://fusion.dominicwatson.co.uk

I'm a bit late returning to this thread. You said that when you assigned cats, it assigned them to ALL the records in the collection. It looks as if when you pass a query to a collection, it does assign the cats to all the rows. So in order to assign different cats per row, you would need to run multiple CFINDEXes. You would NOT need multiple collections. So I'd simply do N queries based on category and add the data like so. If this was covered already in the thread, sorry - responding uqickly. On Mon, Apr 7, 2008 at 6:54 AM, Dominic Watson <watson.dominic@googlemail.com> wrote: ----- Excess quoted text cut - see Original Post for more ----- -- =========================================================================== Raymond Camden, Camden Media Email : ray@camdenfamily.com Blog : www.coldfusionjedi.com AOL IM : cfjedimaster Keep up to date with the community: http://www.coldfusionbloggers.org

Pete, thanks for your insight. That's the way I was going to go initially, before I started wondering if I could use categories in a db query-based collection and if they will allow me to search photos of specific category only. As I said in answers to Dominic's and Ray's posts, categories do work, but not the way I thought they would... I guess I will g the usual way of doing full-text search with Verity and then filtering the db query resultset, which returns a lot more photo data than a Verity collection holds, with results returned from Verity search. I like Dominic's way of doing the filtering, which seems to outperform the IN/NOT IN () filtering... Cheers, Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Pete Ruckelshaus wrote: ----- Excess quoted text cut - see Original Post for more -----

On Tue, Apr 1, 2008 at 1:36 AM, Azadi Saryev <azadi@sabai-dee.com> wrote: > 1) does a database query-based Verity collection support Categories? or >  is it supported only for document/directory based collections? Yes. Remember, to Verity, a collection is a collection. It doesn't matter what 'seeds' it, whether you seed it with physical files, database records, or peanut butter and chocolate. >  2) i am creating a collection and search interface for a multi-faceted >  photo database, where each photo has multiple facets, categories and >  keywords assigned to it. >  while i can create a collection that combines all the >  facets/categories/keywords in the collection's body (together with title >  and description), i think it will work better with facets and categories >  stored as collection/photo categories... Agreed. > >  any thoughts/suggestions? Have you read the CF/Verity docs, specifically those covering how to use categories? It is covered and you should begin there.

Hi Ray, I appreciate your dropping in on the thread - love your blog! My doubt about support of categories for a db query-based collection was due to the lack of info on the subject. I did since find one direct reference on the subject in the CF8 Developers Guide. However, the categories in a collection do not work as I thought they would - they do not 'sort' the items in a collection by category, instead selected categories are just assigned to all items in a collection.  So to be able to allow a user to search photos in selected categories only, i will have to create a separate collection for each category and then search multiple collections... Did I get that right? At least my tests have shown that that's how it works... Another question I thought you may be able to clarify for me: the Verity limit of 125,000 indexed documents on CF8 Standard - is that a limit per collection or for all collections combined? If it is a combined limit then I can easily run over it having multiple collections for each photo facet, category and keyword... Thanks, Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Raymond Camden wrote: ----- Excess quoted text cut - see Original Post for more -----

> If your approach really outperforms the basic recommended IN () > filtering, that's the way forward i guess! I'd say do some tests with and without it with different numbers of preliminary results from the verity search. There's very little difference in the code; just an extra clause in the db query that filters out items not in the verity results. You could even only include the clause if there is a number of verity results below some threshold. I.e. <cfquery name="qDBFilterResults" datasource="#theDatasource#"> * SELECT* * *FROM* tableX *WHERE* filters = #theFilters# <!--- yes, cfqueryparam - could be many filters here ---> <cfif qVerityresults.recordcount LTE someThresholdYouDefine>   AND tableXId IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#ValueList(qVerityResults.key)#" list="true">) </cfif> </cfquery> Of course, you'd only want to do this if you found that it performed better with the IN () for a low number of preliminary results and performed worse with the IN () for a higher number of preliminary results. Just fine tuning really. Dominic -- Blog it up: http://fusion.dominicwatson.co.uk

Thanks Dominic, Good points. On to testing! Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Dominic Watson wrote: ----- Excess quoted text cut - see Original Post for more -----

oh, forgot to ask another question! can, and if yes then how, categoryTree argument be used with a db query-based collection? What's the advantage of defining categoryTree over not defining it? again, there is scarce info available on the subject... If a Verity-indexed photo is assigned facets->categories->keywords, and a category is defined for the assigned keywords, will categoryTree argument hold the parent facet and category for a keyword? how can that be used then in search or search results? thanks, Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Raymond Camden wrote: ----- Excess quoted text cut - see Original Post for more -----