如何使用 CakePHP 查询生成器生成 SQL 函数调用? [英] How to generate SQL function calls with the CakePHP query builder?

问题描述

我有一个作者的全名列，想将姓氏提取到另一列中.我使用以下原始 SQL 执行此操作:

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;

输出:

在使用 SQL 函数"下，Cookbook 说:

Under "Using SQL Functions" the Cookbook says:

除了上述函数之外，func() 方法还可用于创建任何通用的 SQL 函数，例如 year、date_format、convert 等.

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.

但是如何通过 func() 方法创建这个 SUBSTRING_INDEX 函数，以便我可以将它与 CakePHP 查询构建器一起使用?

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

推荐答案

函数构建器带有预定义的方法/函数

FunctionsBuilder 类附带了一堆现成的方法/函数供您使用，例如 sum()、count()、concat()、dateDiff()、now() 等.您可以在如何使用它们在食谱中 和 API 文档.

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.

FunctionsBuilder 类使用魔术方法 __call 处理程序来构建任意 SQL 函数表达式，因此，如果您的函数没有现成的方法，您只需调用您的 SQL 函数:

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]);

这应该大部分是不言自明的，魔术方法名称是SQL函数名称，传递的数组保存应该传递给函数的参数，在这种情况下，第一个和最后一个参数被定义为要处理作为标识符分别作为文字，因此两者都被直接插入到查询中，即不是作为会被转义的绑定参数！

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!

标识符一将另外受制于可能的自动标识符引用，即 name 将被转换为例如 `name`, "name" 或 [name] 取决于正在使用的数据库驱动程序.第二个参数也可以是文字(例如通过传递 '" "')，我只是没有将它设置为一个示例目的.不这样做会导致值被绑定/转换为字符串.

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.

编译后的 SQL 将如下所示:

The resulting compiled SQL will look something like this:

substring_index(name, :c0, -1)

并最终执行为

substring_index(name, ' ', -1) 

处理非硬编码数据，例如用户输入

当处理非硬编码的数据时，即动态的，或以其他方式可能发生变化的数据，请确保在必要时在第二个参数中定义正确的类型以进行强制转换/转义，例如 integer、datetime 等.为了使其正常工作，您必须对列名值使用标识符表达式，否则在使用 <代码>'xyz' =>'标识符' 语法:

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 CakeDatabaseExpressionIdentifierExpression('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
    ]
);

类型将通过数字索引进行匹配，第一个将被忽略，因为它是一个表达式，因此只传递 null.

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.

在您的情况下，您传递不需要作为绑定参数插入到查询中的安全、硬编码的值，并且 SUBSTRING_INDEX 不是由CakePHP 附带的任何方言，您甚至可以使用原始查询来代替 - 但是您将失去在自定义方言中转换表达式的能力，并且自动标识符引用也不再适用，因此只有在您知道时才这样做你在做什么！

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)')

另见

• 食谱 > 数据库访问&ORM > 查询生成器 > 使用 SQL 函数
• API > CakeDatabaseQuery::newExpr()

这篇关于如何使用 CakePHP 查询生成器生成 SQL 函数调用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！