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.
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
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
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.
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.
Yeah I had also read that Verity does support numerical comparisons.
Straight from Adobe's website on "composing search expressions":
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.
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?
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.
Yep, the docs clearly state that those fields can be used with text comparison operators:
(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)..?
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...
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.
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">
WHERE pk_storeProductID IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#keyList#" list="yes">)
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?
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:
<cfset keyList = #Val(ValueList(qryFoundProducts.key))#>
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?
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.
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
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]
p.detail LIKE [some keyword]
p.detail LIKE [some other keyword]
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.
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.
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.
What database are you actually using?
Microsoft SQL Server 2008