LogoLogo
8.1.0
8.1.0
  • Introduction
  • What's New?
  • Installation & Usage
  • Migration Guide
  • Contributing & Filing Issues
  • Query Builder
    • Getting a New Query
    • Building Queries
      • Selects
      • From
      • Joins
      • Wheres
      • Order By
      • Group By and Having
      • Limit, Offset, and Pagination
      • Unions
      • Common Table Expressions (i.e. CTEs)
      • Raw Expressions
      • When / Conditionals
      • Query Parameters and Bindings
    • Executing Queries
      • Retrieving Results
      • Aggregates
      • Inserts, Updates, and Deletes
    • Options and Utilities
      • Query Options
      • Clone
      • Return Format
      • Column Formatter
      • Parent Query
      • Interception Points
    • Debugging
  • Schema Builder
    • Overview
    • Create
    • Columns
    • Column Modifiers
    • Column Constraints
    • Creating Table Constraints
    • Alter
    • Drop
  • External Links
    • API Docs
    • Source Code
    • Issue Tracker
Powered by GitBook
On this page
  • Custom Parameter Types
  • Strict Date Detection
  • Bindings
  • getBindings
  • getRawBindings

Was this helpful?

Edit on Git
Export as PDF
  1. Query Builder
  2. Building Queries

Query Parameters and Bindings

PreviousWhen / ConditionalsNextExecuting Queries

Last updated 4 years ago

Was this helpful?

Custom Parameter Types

When passing a parameter to qb, it will infer the sql type to be used. If you pass a number, CF_SQL_NUMERIC will be used. If it is a date, CF_SQL_TIMESTAMP, and so forth. If you need more control, you can pass a struct with the parameters you would pass to .

You can pass include any parameters you would use with including null, list, etc. This applies anywhere parameters are used including where, update, and insert methods.

QueryBuilder
query.from( "users" )
    .where( "id", "=", { value = 18, cfsqltype = "CF_SQL_VARCHAR" } );
MySQL
SELECT *
FROM `users`
WHERE `id` = ?

This can be used when inserting or updating records as well.

QueryBuilder
query.table( "users" )
    .insert( {
        "id" = { value 1, cfsqltype = "CF_SQL_VARCHAR" },
        "age" = 18,
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_DATE" }
    } );
MySQL
INSERT INTO `users`
    (`id`, `age`, `updatedDate`)
VALUES
    (?, ?, ?)

Strict Date Detection

By default, qb will try to determine if a variable is a date using the built-in isDate function. This can have some interesting effects with different formatted strings. You can opt in to stricter date detection which will check the underlying Java class of the value to determine if the value is a date. This is more accurate, but does require you to specifically pass date instances instead of strings. For this reason, it is currently opt-in to not break existing applications. It is likely to become the default in the next major version of qb.

You can opt in to stricter date detection by setting strictDateDetection = true in your moduleSettings in config/ColdBox.cfc.

moduleSettings = {
    "qb": {
        "strictDateDetection": true
    }
};

Bindings

If you need to inspect the bindings for the current query you can retrieve them in order using the getBindings method.

Use these methods only for debugging. Modifying the bindings directly will likely cause issues when executing your query. Adding or removing bindings should be done using the public API.

getBindings

Name

Type

Required

Default

Description

No arguments

This method returns the current bindings in order to be used for the query.

QueryBuilder
query.from( "users" )
    .join( "logins", function( j ) {
        j.on( "users.id", "logins.user_id" );
        j.where( "logins.created_date", ">", dateAdd( "m", -1, "01 Jun 2019" ) );
    } )
    .where( "active", 1 );
Result
[
    { value = "01 May 2019", cfsqltype = "CF_SQL_TIMESTAMP"  },
    { value = 1, cfsqltype = "CF_SQL_NUMERIC" }
]

You can also retrieve the bindings associated to their corresponding types.

getRawBindings

Name

Type

Required

Default

Description

No arguments

This method returns the current bindings to be used for the query associated to their corresponding types.

QueryBuilder
query.from( "users" )
    .join( "logins", function( j ) {
        j.on( "users.id", "logins.user_id" );
        j.where( "logins.created_date", ">", dateAdd( "m", -1, "01 Jun 2019" ) );
    } )
    .where( "active", 1 );
Result
{
    "commonTables" = [],
    "select" = [],
    "join" = [
        { value = "01 May 2019", cfsqltype = "CF_SQL_TIMESTAMP"  },
    ],
    "where" = [
        { value = 1, cfsqltype = "CF_SQL_NUMERIC" }
    ],
    "union" = [],
    "insert" = [],
    "insertRaw" = [],
    "update" = []
};

Bindings are the values that will be sent as parameters to a prepared SQL statement. This protects you from In CFML, this uses to parameterize the values.

You can view the current SQL for the query with bindings inline for debugging purposes using the method.

cfqueryparam
cfqueryparam
SQL injection.
cfqueryparam
toSQL