ColdFusion Verity Search Float Problem

Report · Oct 07, 2010

I have a database full of products. One of the fields for each product in the database is a minimum price. I'm trying to create a search that says something like "find all products with a minimum price between X and Y." However, when it returns results, it includes numbers that are out of the range. For instance, if I say between 2 and 3 dollars, it will return the correct results but will include numbers like 25.00 and 245.00, and when I do something like "between 4 and 5 dollars" it returns the correct results with additional numbers like "40.00" and "434.00." So it seems to me that ColdFusion can't process the floating number correctly...It sees a number like "40.00" and assumes its in the range between 4 and 5. Here is my code for the indexing:

<cfquery name="v_qryProductInfo" datasource="#application.DSN#">
SELECT DISTINCT    SP.pk_storeProductID,
                P.productName,
                C.categoryName,
                P.prodTimeMin,
                P.prodTimeMax,
                PD.miniDesc,
                PD.keywords,
                CONVERT(float, (dbo.getMinPrice(pk_storeProductID))) AS minPrice
FROM             tblProduct P
INNER JOIN        tblProductDescription PD ON P.pk_productID = PD.fk_productID
INNER JOIN        tblStoreProduct SP ON P.pk_productID = SP.fk_productID
INNER JOIN        tblProductSubCategory PSC ON SP.pk_storeProductID = PSC.fk_productID
INNER JOIN        tblSubCategory SC ON SC.pk_subCategoryID = PSC.fk_subCategoryID
INNER JOIN        tblCategory C ON C.pk_categoryID = SC.fk_categoryID
WHERE            (SP.blnStoreActive = 1)
AND                (SP.fk_storeID = #application.storeID#)
</cfquery>
<cfdump var="#v_qryProductInfo#">
<h1>Indexing data...</h1>
<cfindex
    action="refresh"
    collection="#application.vcCollection#"
    key="pk_storeProductID"
    type="custom"
    title="productName"
    query="v_qryProductInfo"
    body="miniDesc, keywords, pk_storeProductID, minPrice"
    Custom1="minPrice"
    Custom2="prodTimeMin"
    Custom3="prodTimeMax"
    Custom4="categoryName"
>

My cfsearch tag criteria looks like this:

    <cfsearch
        criteria="CF_CUSTOM1 >= #priceRangeMin# <AND> CF_CUSTOM1 <= #priceRangeMax#"
        name="qryFoundProducts"
        collection="#application.vcCollection#">

I've made the Custom1 column for the indexing the "minPrice" which is calculated as a function in my database. I've even tried making sure the number that is being entered in the Custom1 index is a float by using the CONVERT function in my SQL code. Any ideas why ColdFusion can't properly relate a float? It seems like it's trying to compare a string and a float, which isn't working too well.

Any info would be greatly apprecaited.

Report · Oct 07, 2010

It isn't a precision problem it is a text versus number problem.

Numerically, 40.00 and 434.00 are not between 4 and 5. But alphabetically they are!

Your system is treating these numbers as text and sorting them accordingly. Can't say if that is a problem with Verity, ColdFusion or your code, but that is the type of sorting going on.

Message was edited by: ilssac P.S. But since Verity is a TEXT searching tool, it may not be in it's nature to look at data any other way. Why are you applying the filter through Verity rather then the base database query?

Message was edited by: ilssac But if you insist on using a text indexing tool that can only see all data as text, you need to make your numbers sortable as text which would involve prepending zeros so that all the numbers have the same number of digits. I.E. 004.00,040.00,434.00, and 005.00, then asking for data between 004.00 and 005.00 would return the desired results. But I would still suggest this is more properly done in the database query.

Report · Oct 07, 2010

Well the reason I'm trying to do this in a cfsearch tag is because it will later be combined with other search criteria that IS text based...i.e. find all products in category A that have a production time of B and a price range between X and Y with keywords M, N, and O. I'm using one temporary Verity collection that is being indexed/refreshed every time one of these searches happen, so each search criteria is built off the last search. If I had to do all of these with just queries, the keyword and category matching would be difficult to do in SQL.

Any ideas? I really do appreciate the feedback though...I had a feeling Verity treated everything as text and that's why it was having a hard time comparing the numbers.

I'd also like to add that although I've been working with ColdFusion for a while now, I am fairly new to the Verity search functions in ColdFusion...So there may be a better way to do this that I'm not aware of.

Message was edited by: wcx08

Report · Oct 07, 2010

Verity can't do numeric comparisons. So, here's what you need to do. First, search Verity for whatever other (text) criteria you have. Then, use the primary keys you pull from the Verity search to search your database using the additional numeric criteria.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

Report · Oct 07, 2010

I'm not so sure. The docs I found did specifically say numerical

comparisons were supported using <, >. Obviously it isn't working for

him but it may be something else.

Report · Oct 07, 2010

Yeah I had also read that Verity does support numerical comparisons.

Straight from Adobe's website on "composing search expressions":

Relational operators

Relational operators search document fields (such as AUTHOR) that you defined in the collection. Documents containing specified field values are returned. Documents retrieved using relational operators are not ranked by relevance, and you cannot use the MANY modifier with relational operators.

You use the following operators for numeric and date comparisons:

And it goes on to show the relational operators and examples using numbers. I figured that since ColdFusion is loosely typed, it would know when to turn a string in to a double or float. I guess I was wrong? Does anyone else have this problem?

Report · Oct 07, 2010

I didn't check the documentation, but your problem may center around using the "custom" fields. There is probably no type on those fields so verity types them as strings.

Report · Oct 09, 2010

Yep, the docs clearly state that those fields can be used with text comparison operators:

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_r-s_13.html

(and it's the same in CF9).

I dunno if Solr is an option for you and whether it supports this sort of thing (but I would presume it does)..?

--

Adam

Report · Oct 09, 2010

I do have the option of making the collection Solr...Would this support numerical comparison

instead of text-only? I think it's silly that Verity supports text-only comparison...

Report · Oct 09, 2010

I don't know a great deal about Solr or Lucene, but it's all thoroughly documented on the Apache website. By comparison the CF docs for Solr integration are woefully lightweight. I would not expect to learn much from those.

I'm sure Verity supported all this (I say supportED, because it does not exist as a product any more). It's most likely CF that doesn't implement it properly. That's just a guess though.

--

Adam

Report · Oct 12, 2010

I took the previously stated advice to do Verity searches for the text-oriented search criteria, and then use the primary keys from this query to search my database to then do the numerical comparisons. Now my problem is coming from the fact that the keys themselves are strings too...So my key column of my Verity query, despite being numbers, are being processed as strings. When I go to do a regular database query using these keys, it can't do the comparison between integers and strings. I've tried using the Val() function, but to no avail. Here's my code:

        <cfset keyList = #Val(ValueList(qryFoundProducts.key))#>

        <cfquery name="qryFoundProducts" dbtype="query">
            SELECT            *
            FROM            v_qryProductInfo
            WHERE            pk_storeProductID IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#keyList#" list="yes">)
        </cfquery>

qryFoundProducts is the name of the Verity query that is done before this code is called. The problem is occurring in the WHERE clause of my SQL statement.

I'm starting to conclude that Verity does not support numbers of ANY kind...It's purely for string-based criteria only. What was Adobe thinking?

Report · Oct 12, 2010

Well, firstly: don't blame Adobe for Verity... Verity is (well: was) a separate product, and I don't think Verity as a company has even existed during Adobe's tenure at the helm (to mix metaphors awkwardly) of CF.

That said, one perhaps can blame Adobe for any disconnect between Verity and CF's support thereof, if Verity does actually support numeric processing, and CF mungs that somehow. I'm not saying this is the case, but who knows?

What Adobe perhaps didn't do is ditch or deprecate Verity earlier than it did. Lucene's been around for ages, and has been solid for ages, so I don't know why they didn't revise their search engine support earlier than they did. Oh well.

Now: your issue.

First thing, this won't work:

val() returns the value of a number, whereas valueList() returns... a list. So it val() works at all, the result will just be the value of the first number in the list. You also don't need the pound-signs there, but either way, it won't affect your situation.

Second, as CF is loosely typed, it doesn't matter if you pass a numeric string or an actual number to a <cfqueryparam> tag, if you've said the value is supposed to be an integer (CF_SQL_INTEGER), then CF will automatically convert it to an integer if it can. So provided the values in your query column can be expressed as integers, they will convert with no intervention on your part.

So, in that light, I'm not sure your problem is necessarily what you think it is..?

But you say there's a problem in your WHERE clause, but you don't actually say what the problem actually is. Are you getting an error? Or just no records? The wrong records?

What do you see if you dump the query out? If you take the values output by valueList() and execute the query with those values in a different DB client (like MS SQL Studio or whatever), do you get the results you expect?

What sort of data is in the Verity collection btw? Are they documents, or DB data, or a mix, or what? If there are PKs coming out of the collection, it sounds like the data came from a DB in the first place. Can you not use full-text searching in your DB, and leave Verity out of the equation?

--

Adam

Report · Oct 12, 2010

The reason I was using the Val() function is because the key data from my Verity query is a string and in order to compare it to an integer in my SQL, I needed to comvert it to an integer. I was getting an error that it couldn't compare an integer (left hand side) to a string (right hand side). My main problem with the code I posted above was that ValueList(qryFoundProducts.key) was only returning one value for some reason when it should have been returning many. My verity collections are being indexed straight from my database, so I'm not mixing documents and DB. I have to use both Verity and database queries because I'm trying to implement an advanced search functionality that will do something similar to, "Find all products in category X with keywords A, B, C, with a production time between 1 and 5 days with a price range between 3 and 4 dollars." So yes, I need to be able to search for keywords as well as numerical comparisons. Verity is good for one but not the other....Database queries are good for one but not the other. So I'm kinda stumped.

Report · Oct 12, 2010

OK, so your first problem is that you're expecting a bunch of rows back from your Verity search, but there's only one. That'd be why

the valueList() is only returning one.

Have you dumped your Verity results to confirm you're getting what you're expecting.

Also, I see nothing about your query criteria that would be tricky on a DB (even without using full-text searching). Which bit are you thinking would cause a problem?

I mean it's just something like

SELECT [whatever]

FROM product p

INNER JOIN prodcatlink pcl

ON p.id = pcl.prod_id

INNER JOIN category c

ON pcl.cat_id = c.id

WHERE c.label = [you category here]

AND p.date BETWEEN [lower threshold] AND [upper threshold]

AND p.price BETWEEN [lower threshold] AND [upper threshold]

AND (

p.detail LIKE [some keyword]

OR

p.detail LIKE [some other keyword]

OR

p.detail LIKE [etc]

)

Obviously that schema is appalling, and I would not do the LIKE OR LIKE OR LIKE thing in reality (I'd use full text searching!), but you get my drift.

Not difficult.

--

Adam

Report · Oct 12, 2010

wcx08 wrote:
My main problem with the code I posted above was that ValueList(qryFoundProducts.key) was only returning one value for some reason when it should have been returning many.

No, the val() function around the valueList() function was returning only one value, the first. That is what val() does, it returns one and only one number if it can find one at the beginning of a string. Val(), does not work on a series of numbers like a list.

IF you must do this, you will have to loop over the list and build the IN clause individually form each iterations val() value. But I am with Adam on this, that should have been unecessary and automatically handled by the <cfqueryparam...> tag and its cf_sql_type parameter.

Report · Oct 12, 2010

Well in my search I have four criteria available to the the user: category, keywords (any word, any amount), production time (two integers), and price range (two integers). Since the category criteria is a drop down selection menu and is being pulled from the same place that will be searched against, I can use a regular database query for category, production time, and price range. But how can I use a regular database query for something like an indefinite amount of random, user-supplied keywords? I'm beginning to think I should stray away from using Verity at all if I can...It would just make combining all of these searches easier.

Report · Oct 12, 2010

What database are you actually using?

--

Adam

Report · Oct 12, 2010

Microsoft SQL Server 2008

Report · Oct 12, 2010

Read this lot:

http://msdn.microsoft.com/en-us/library/ms142571.aspx

--

Adam

Adobe Community

ColdFusion Verity Search Float Problem

Relational operators