2

Part of a search query i'm building requires filter values to be processed through an SQL query to check against a CSV list within a database field. (I have no control over the use/non use of CSV lists within database fields, working with what i have) and I have done a little testing and found that you can do the following:

Where database_field In (#CSV_list#)

If database_field equalled 2 and CSV_list equalled 1,2,3,4 this would return true as the value of 2 was found within the CSV list.

However, the issue i have is i needed to check a list of values against the field, rather than the field against the list. Ill show you how i did this.

<cfset URL.filter_sizes = [2,4,7,10]>

<cfif IsDefined("URL.filter_sizes")>
    <cfset filterList = mid(URL.filter_sizes, 2, len(URL.filter_sizes) - 2)>

    And (
        <cfloop list="#filterList#" index="filter_item">
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#filter_item#"> In 
            (item_sizes)
            <cfif listLast(filterList, ",") neq filter_item> 
             Or</cfif>
        </cfloop>
    )
</cfif>

Now, some of you may think "oh my god this is a VERY inefficient way of doing things. I know, but one thing i have found through trial and error is once i get a method working, i always find a way of optimizing it. There is no point targetting perfection first time on something i have never tried before. Anyway off tangent, as you can see it loops my filterList variable and checks each value against the item_sizes field to see if the value is in that CSV list.

Here's the catch. If item_sizes equalled 2, great, because one of the values would be 2 and it would match true. However if item_sizes equalled 2,3,4, even though two of my variable values are 2 and 4, it does not match the STRING of 2,3,4 (something which i found happens via trial and error). My guess, is that even though i've put the value on the left and the field on the right, it still runs the line as item_sizes In (#filter_item#), this is the only logical answer i could come up with.

Does anybody have any idea how i could solve this issue (without normalizing the database as this is not possible at this current moment in time) by using the method i've used above?

SOLUTION

As quoted in the comments, i found a makeshift solution to this problem. It isn't the best, however it DOES work.

By using the following syntax, you can get the desired results:

<cfset URL.filter_sizes = [2,4,7,10]>

<cfif IsDefined("URL.filter_sizes")>
    <cfset filterList = mid(URL.filter_sizes, 2, len(URL.filter_sizes) - 2)>

    And (
        <cfloop list="#filterList#" index="filter_item">
            ',' + item_sizes + ',' Like
            <cfqueryparam cfsqltype="cf_sql_varchar" value="%,#filter_item#,%">
            <cfif listLast(filterList, ",") neq filter_item> Or</cfif>
        </cfloop>
    )
</cfif>

PLEASE NOTE

The use of <cfset URL.filter_sizes = [2,4,7,10]> is to emulate what the actual URL variable would be (after editing). This is not a mistake where I have used a struct within a URL variable.

Banny
  • 811
  • 4
  • 13
  • 36
  • 2
    I ran part of your code and it crashed. url.filter_sizes is an array and you are attempting to run string functions on it. In any event, your query logic is wrong. The list in your database is not really a list, it's a string. Therefore you have to treat it as a string with the keyword "like". – Dan Bracuk Nov 27 '13 at 13:34
  • You used it in literal terms that is why. I simply used `` to demonstrate what the URL variable is. That is how i display them in the URL variable (and how other large sites) handle multiple values within one URL variable. As for using the keyword like, that is correct and is how i have gone about it. I solved the issue by using `Where ',' + database_field + ',' Like `. – Banny Nov 27 '13 at 15:58
  • 1
    Compliments on your solution. I suggest that you post it as an answer to make it more visible for the next person with this problem. – Dan Bracuk Nov 27 '13 at 16:40
  • @LeeB - It is better to post "solutions" as a separate answer. Then it is more visible to others. Side note, consider using a `from/to` loop instead of `list`. Reason being the current code could generate a syntax error if the last element was repeated. – Leigh Nov 29 '13 at 14:48

0 Answers0