Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
query-based Verity collection with Ctegories?
Author: Raymond Camden
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302872
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.
----- 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
Author: Dominic Watson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302836
----- Excess quoted text cut - see Original Post for more -----
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
Author: Azadi Saryev
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302824
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 -----
Author: Dominic Watson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302807
> 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
Author: Azadi Saryev
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302804
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 -----
Author: Azadi Saryev
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302685
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 -----
Author: Dominic Watson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302684
> 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
Author: Azadi Saryev
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302683
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 -----
Author: Azadi Saryev
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302681
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 -----
Author: Azadi Saryev
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302680
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 -----
Author: Azadi Saryev
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302679
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 -----
Author: Raymond Camden
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302478
> 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.
Author: Dominic Watson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302475
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 -----
Author: Pete Ruckelshaus
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302474
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
Author: Azadi Saryev
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302470
i guess nobody uses Verity? :)
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
Author: Azadi Saryev
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55828#302409
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/
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
