Known Participant

Question

ColdFusion Verity Search Float Problem

Forum|Forum|15 years ago
October 7, 2010
2 replies
3002 views

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.

Advanced techniques

This topic has been closed for replies.

W

wcx08Author

Known Participant

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?

A

Adam Cameron.

Inspiring

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

W

wcx08Author

Known Participant

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.

ilssac

Inspiring

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.

W

wcx08Author

Known Participant

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

D

Dave Watts

Community Expert

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.

Dave Watts, Eidolon LLC

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded