How to generate SQL function calls with the CakePHP query builder?

Question

I have a fullname column for authors and would like to extract the surname into another column. I do that with the following raw SQL:

SELECT name,
SUBSTRING_INDEX(`name`, ' ', -1) AS `surname`
FROM qr.authors;

Output:

Under "Using SQL Functions" the Cookbook says:

In addition to the above functions, the func() method can be used to create any generic SQL function such as year, date_format, convert, etc.

But how can I create this SUBSTRING_INDEX function through the func() method so that I can use it with the CakePHP query builder?

Mertens

Answer 1

The functions builder comes with predefined methods/functions

The FunctionsBuilder class ships with a bunch of ready-made methods/functions for you to use, like sum(), count(), concat(), dateDiff(), now(), etc. You can find a complete list of the supported functions and examples on how to use them in the Cookbook and the API docs.

Arbitrary functions can be built by just calling them

The FunctionsBuilder class uses the magic method __call handler to build arbitrary SQL function expressions, so in case there is no ready made method for your function, you can just "call" your SQL function:

$query = $this->SomeTable->find();

$func = $query->func()->substring_index([
    'name' => 'identifier',
    ' ',
    -1 => 'literal'
]);
$query->select([/* ... */, 'surname' => $func]);

This should be mostly rather self explanatory, the magic method name is the SQL function name, and the passed array holds the arguments that should be passed to the function, where in this case the first and last argument are defined to be treated as identifier respectively as a literal, and thus both being inserted into the query directly, ie not as bound parameter that would be escaped!

The identifier one will additionally be subject to possible automatic identifier quoting, ie name would be transformed to for example `name`, "name", or [name] depending on the database driver in use. The second argument could be made a literal too (by passing for example '" "'), I've just not set it as one for example purposes. Not doing so will cause the value to be bound/casted as a string.

The resulting compiled SQL will look something like this:

substring_index(name, :c0, -1)

and will finally be executed as

substring_index(name, ' ', -1) 

Handling non-hard-coded data, for example user input

When working with data that isn't hard-coded, ie dynamic, or otherwise subject to possible change, make sure that you define the proper types for casting/escaping in the second argument if necessary, like integer, datetime, etc. In order to get this working properly, you'll have to use an identifier expression for the column name value, as otherwise the second argument would be ignored when using the 'xyz' => 'identifier' syntax:

$func = $query->func()->substring_index(
    [
        new \Cake\Database\Expression\IdentifierExpression('title'),
        ' ',
        $userInput,
    ],
    [
        null,     // no typecasting for the first argument
        'string', // second argument will be bound/casted as string
        'integer' // third argument will be bound/casted as integer
    ]
);

The types will be matched via the numeric indices, and the first one is going to be ignored, since it is an expression, hence passing just null.

You could even use raw expressions

In your case, where you are passing safe, hard-coded values that don't need to be inserted into the query as bound parameters, and SUBSTRING_INDEX isn't a function that is covered by any of the dialects that ship with CakePHP, you could even use raw queries instead - you'll however loose the ability to transform the expression in custom dialects, and automatic identifier quoting will also not apply any more, so only do this if you know what you are doing!

$query->newExpr('SUBSTRING_INDEX(`name`, "", -1)')

See also

• Cookbook > Database Access & ORM > Query Builder > Using SQL functions
• API > \Cake\Database\Query::newExpr()

Mango