CODEWITHSUNDEEP
t-sqlcoldfusioncoldfusion-8qoq
Eric Belair
Eric Belair

Reputation: 10692

Why am I getting erroneous records in a Query-of-Queries with LIKE conditions?

I have an ColdFusion 8 application that caches a large query every 2 minutes using a Scheduled Task. This query contains product data. This data is used in AJAX calls for auto-complete logic, which is the primary reason it is cached, since it takes a few seconds to run each time.

In the application, there are several pages that query from this query result using Query-of-Queries.

On of these queries uses a LIKE condition to filter the results. However, the query is returning records that do not meet the conditions. Why is this happening and how can I prevent it?

My Cached Query:

<cfquery name="qProducts" datasource="#APPLICATION.DataSource#">
    SELECT
        P.SKU
        P.DESC,
        P.TITLE,
        P.KEYWORDS,
        X.ATTR1
    FROM    PRODUCTS P
        LEFT JOIN PRODUCTSEXT X
            ON P.SKU = X.EXTSKU
    WHERE   P.ACTIVE = 1
</cfquery>

My Filter Query:

<cfquery name="_qFilteredProducts" dbtype="Query">
    SELECT
        SKU
        DESC,
        TITLE,
        KEYWORDS
    FROM    qProducts
    WHERE   1 = 1
        AND (
                DESC LIKE '%#searchstring#%'
            OR  TITLE LIKE '%#searchstring#%'
            OR  KEYWORDS LIKE '%#searchstring#%'
            OR  ATTR1 LIKE '%#searchstring#%'
            )
</cfquery>

So, let's say I pass in the value "gold" for searchstring.

I am seeing results like the following:

SKU               DESC                TITLE         KEYWORDS
-------------------------------------------------------------------------------
ABC               GOLD KEY            GOLD KEY      GOLD KEY SHINY
DEF               SILVER KEY          SILVER KEY    SILVER KEY SHINY

I should not see that second record.

Upvotes: 1

Views: 69

Answers (1)

Eric Belair
Eric Belair

Reputation: 10692

I think I figured this out by doing a little more research on the internet. My original query actually has a LEFT JOIN in it, and the LIKE clause in ColdFusion's QoQ tends to completely ignore NULL values, so I simply had to update my original/database query to change all NULL values to empty string.

<cfquery name="qProducts" datasource="#APPLICATION.DataSource#">
    SELECT
        P.SKU
        P.DESC,
        P.TITLE,
        P.KEYWORDS,
        IFNULL(X.ATTR1, '')
    FROM    PRODUCTS P
        LEFT JOIN PRODUCTSEXT X
            ON P.SKU = X.EXTSKU
    WHERE   P.ACTIVE = 1
</cfquery>

Upvotes: 3

Related Questions