Coldfusion find if column has value

Question

I have a table which has a column LanguageID. It has seven values: 1, 2, 3, 4, 5, 6, 22

Now I have a function that gets the languageID of the current user.

I want to check if that languageID exists in my table, if it exists return that value, if not then return default 2. If 2 doesn't exist in that table then return 1.

This is my query:

<cfquery name="NameName" datasource="mydatabase">
        SELECT DISTINCT SomeID,
        SomeName,
        LanguageID
        FROM myDatabase WITH(NOLOCK)
        WHERE BlacklistReasonID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.BlacklistReasonID#" />
        <cfif isDefined(arguments.LanguageID)> //I want to check if the arguments.LanguageID exists in the table
            AND LanguageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.LanguageID#" />
        <cfelseif NOT isDefined(arguments.LanguageID) > //ako nema value
            AND LanguageID = 2 //default, put languageID to 2
        <cfelse>
            AND LanguageID = 1 //if languageID 2 doesn't exist in that table, then put 1
        </cfif>
    </cfquery>

So real life example. The funtion activates, the LanguageID is 3. That's ok, the first cfif check to see if the table has a value of 3, it has, ok then it puts the languageID to 3 and ends.

Second scenario. The funtion activates, the LanguageID is 44. The first cfif checks to see if the table has a value of 44, it doesn't have that value, so the second cfif activates and puts the LanguageID to 2.

Answer 1

This can be done in the SQL itself. That would likely be much more performant than having ColdFusion try to do query processing.

I made a couple of assumptions, based on your original question. First, that this query should only return 1 result (TOP 1). And second, that your default languages are 2 for Default1 and 1 for Default2. This also includes that the value you are searching for will be greater than 1 or 2.

SELECT TOP 1 SomeID, SomeName, LanguageID
FROM (
  SELECT SomeID, SomeName, LanguageID
  FROM t1
  WHERE BlacklistReasonID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.BlacklistReasonID#">
    AND LanguageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Language#">

  UNION ALL 

  SELECT SomeID, SomeName, LanguageID
  FROM t1 
  WHERE BlacklistReasonID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.BlacklistReasonID#">
    AND LanguageID IN ( <cfqueryparam value="1,2" cfsqltype="CF_SQL_INTEGER" list="yes"> ) /* DEFAULT IDs */
) s1
ORDER BY id DESC
;

This works because a query will return no results if the filtering condition is not matched, but when joined to a query that does have results (your defaults) those will appear in the whole query. Then I order the results and take the top 1.

The basic gist of the query is at: https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=6f729939d3305e49032eab56d88aa877

I also included several demonstrations of how this works if your LanguageID is not found, and one possible way of using default IDs that aren't 1 or 2.

---

And to keep it all in one place:

CREATE TABLE t1 ( id int, lang varchar(10) ) ;
INSERT INTO t1 (id, lang)
VALUES (1,'Default 2'),(2,'Default 1'),(3,'Klingon')
  ,(50, 'Common'),(20, 'Nadsat'),(55, 'Furbish')
;
/* If you will only have one language per id, and default IDs will always be 2 and 1 */
/* Match */
SELECT TOP 1 id, lang
FROM (
  SELECT id, lang 
  FROM t1
  WHERE id = 3

  UNION ALL 

  SELECT id, lang
  FROM t1 
  WHERE id IN (1,2) /* DEFAULT IDs */
) s1
ORDER BY id DESC
;

id | lang   
-: | :------
 3 | Klingon

/* No match. */
SELECT TOP 1 id, lang
FROM (
  SELECT id, lang 
  FROM t1
  WHERE id = 99999

  UNION ALL 

  SELECT id, lang
  FROM t1 
  WHERE id IN (1,2) /* DEFAULT IDs */
) s1
ORDER BY id DESC
;

id | lang     
-: | :--------
 2 | Default 1

/* No Default1 Language */
CREATE TABLE t2 ( id int, lang varchar(10) ) ;
INSERT INTO t2 (id, lang)
VALUES (1,'Default 2'),(3,'Klingon')
  ,(50, 'Common'),(20, 'Nadsat'),(55, 'Furbish')
;

SELECT TOP 1 id, lang
FROM (
  SELECT id, lang 
  FROM t2
  WHERE id = 9999

  UNION ALL 

  SELECT id, lang
  FROM t2 
  WHERE id IN (1,2) /* DEFAULT IDs */
) s1
ORDER BY id DESC
;

id | lang     
-: | :--------
 1 | Default 2

/* No Default Language */
CREATE TABLE t3 ( id int, lang varchar(10) ) ;
INSERT INTO t3 (id, lang)
VALUES (3,'Klingon')
  ,(50, 'Common'),(20, 'Nadsat'),(55, 'Furbish')
;

/* No match. No Default. */
SELECT TOP 1 id, lang
FROM (
  SELECT id, lang 
  FROM t3
  WHERE id = 9999

  UNION ALL 

  SELECT id, lang
  FROM t3 
  WHERE id IN (1,2) /* DEFAULT IDs */
) s1
ORDER BY id DESC
;

id | lang
-: | :---

/* Match, but no default */
SELECT TOP 1 id, lang
FROM (
  SELECT id, lang 
  FROM t3
  WHERE id = 3

  UNION ALL 

  SELECT id, lang
  FROM t3 
  WHERE id IN (1,2) /* DEFAULT IDs */
) s1
ORDER BY id DESC
;

id | lang   
-: | :------
 3 | Klingon

/* Default 1 and 2 are not ID 2 and 1. */
CREATE TABLE t4 ( id int, lang varchar(10) ) ;
INSERT INTO t4 (id, lang)
VALUES (40,'Default 2'),(42,'Default 1'),(3,'Klingon')
  ,(50, 'Common'),(20, 'Nadsat'),(55, 'Furbish')
;

/* No match. Pick Default. */
SELECT TOP 1 s1.id, s1.lang
FROM (
  SELECT id, lang 
  FROM t4
  WHERE id = 9999

  UNION ALL 

  SELECT s2.id, s2.lang 
  FROM (
    SELECT TOP 1 id, lang
      , CASE 
          WHEN ID = 42 THEN 2 
          WHEN ID = 40 THEN 1 
          ELSE 0 
        END AS sortOrder
    FROM t4 
    WHERE id IN (40,42) /* NEW DEFAULT IDs */
    ORDER BY sortOrder DESC
  ) s2
) s1
ORDER BY s1.id DESC
;

id | lang     
-: | :--------
42 | Default 1

db<>fiddle here