cfsearch with a category restriction
ColdFusion’s Verity is difficult to figure out. There’s hardly any documentation for it online and there are a number of quirky little issues that cause a lot of frustration. Today I was trying to do what seemed to be rather simple, at least according to the Adobe docs: searching a collection that contains categories.
I have a table of articles, a table of categories, and a relational table joining the two. It’s a many-many relationship (an article may have multiple categories and a category may have multiple articles). I also have a collection created in CF Administrator named articles_index with the categories option enabled.
With the following code I queried and indexed the data from my articles table, and included a comma-list (generated by a MS SQL UDF) of custom categories in the cfindex’s category param, see below:
<cfquery name="IndexArticles" datasource="webdsn">
SELECT *,
dbo.CategoryIDList(articleID) CatIDList
FROM dbo.Articles
</cfquery>
<cfindex
query="IndexArticles"
collection="articles_index"
action="refresh"
type="Custom"
key="articleID"
title="articleID"
body="articleName,articleAuthor,articlePubDate,articleDescription,articleKeywords"
category="CatIDList">
<h3>indexing complete</h3>
And if you’re curious, this is how my UDF looks in MS SQL that created the CatIDList (called in the IndexArticles SELECT statement above):
CREATE FUNCTION dbo.CategoryIDList(@articleID char(35)) RETURNS VARCHAR(1000) AS BEGIN DECLARE @CatIDList varchar(1000) SELECT @CatIDList = COALESCE(@CatIDList + ',', '') + RTRIM(articleID) FROM dbo.Category INNER JOIN dbo.Document ON clocatID = clocatdocCategory INNER JOIN dbo.articleument ON clocatdocDocument = articleID WHERE articleID = @articleID RETURN @CatIDList END
RSS