Nando @ Aria Media

( learning Clojure ColdFusion Lucee Javascript jQuery Angular CSS Linux Apache HTML5 & etc )

CFML Queries in Script

| Comments

Recently, I needed to write a somewhat complex query with a conditional where clause. I resisted the urge to revert to the cfquery tag for this sort of thing, and came up with the following using queryExecute(), submitted as an example without explanation. This approach works on both ACF and Lucee.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
public query function findRawAudit( string entity, numeric userId, date dateFrom, date dateTo, numeric timeZoneOffset ) {
  var newLine = CHR(13) & CHR(10);
  var params = {};
  var sql = "
      select a.entity, a.entityId, a.timestamp, a.transaction, a.attribute, a.value as previousValue, u.name as username, null as currentValue, null as auditName, null as asql
      from Audit a inner join User u
      on a.userId = u.userId
      where 0=0
  ";

  if ( structKeyExists(arguments, 'entity') and len( arguments.entity ) ) {
      sql &= newLine & "and entity = :entity";
      structInsert(params, "entity", { value: arguments.entity, cfsqltype: "cf_sql_varchar" } );
  }

  if ( structKeyExists(arguments, 'userId') ) {
      sql &= newLine & "and a.userId = :userId";
      structInsert(params, "userId", { value: arguments.userId, cfsqltype: "cf_sql_integer" } );
  }

  if ( structKeyExists(arguments, 'dateFrom') ) {
      sql &= newLine & "and timestamp > :dateFrom";
      //  adjust dateFrom to account for time zone difference between server and user
      var dateFromUTC = DateAdd( 'h', -arguments.timeZoneOffset, arguments.dateFrom );
      structInsert(params, "dateFrom", { value: arguments.dateFrom, cfsqltype: "cf_sql_timestamp" } );
  }

  if ( structKeyExists(arguments, 'dateTo') ) {
      sql &= newLine & "and timestamp < :dateTo";
      //  adjust dateFrom to account for time zone difference between server and user
      var dateToUTC = DateAdd( 'h', -arguments.timeZoneOffset, arguments.dateTo );
      // we add a day to dateTo to arrive at an effectiveEndDateTime, namely midnight of the endDate
      // so that the endDate is inclusive, as users would very likely expect
      dateToUTC = dateAdd( 'd', 1, dateToUTC );
      // stdout.println( "dateToUTC : #dateToUTC #" );
      structInsert(params, "dateTo", { value: dateToUTC, cfsqltype: "cf_sql_timestamp" } );
  }

  sql &= newLine & "and a.attribute != 'version' ";

  sql &= newLine & "and a.attribute != 'lastEdited' ";

  sql &= newLine & "and a.attribute != 'userId' ";

  sql &= newLine & "order by a.timestamp desc, a.transaction";

  return queryExecute( sql, params );
}

Thanks to Igal Sapir for helping me work this out!

Comments