Query Parameters and Bindings

Custom Parameter Types

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

QueryBuilder
query.from( "users" )
    .where( "id", "=", { value = 18, cfsqltype = "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 = "VARCHAR" },
        "age" = 18,
        "updatedDate" = { value = now(), cfsqltype = "DATE" }
    } );
MySQL
INSERT INTO `users`
    (`id`, `age`, `updatedDate`)
VALUES
    (?, ?, ?)

Numeric SQL Types

qb will use a different SQL type for integers and decimals. You can customize the SQL types by setting the integerSqlType and decimalSqlType settings.

moduleSettings = {
    "qb": {
        "integerSqlType": "INTEGER",
        "decimalSqlType": "DECIMAL"
    }
};

Additionally, qb automatically calculates a scale based on the value provided if the value is a floating point number.

Bindings

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

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

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

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 = "TIMESTAMP"  },
    { value = 1, cfsqltype = "INTEGER" }
]

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" = []
};

addBindings

Adds a single binding or an array of bindings to a query for a given type.

Name
Type
Required
Default
Description

newBindings

Struct | Array<Struct>

true

A single binding or an array of bindings to add for a given type.

type

String

false

"where"

The type of binding to add.

addBindingsFromBuilder

Adds all of the bindings from another builder instance.

Name
Type
Required
Default
Description

qb

QueryBuilder

true

Another builder instance to copy all of the bindings from.

Was this helpful?