cakephp - 如何使用 CakePHP 查询生成器生成 SQL 函数调用？

Question

我有一个作者的全名列，并且想将姓氏提取到另一列中。我使用以下原始 SQL 来做到这一点:

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

输出:



Cookbook 在“使用 SQL 函数”下说:

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 查询构建器一起使用？

Answer 1

函数构建器带有预定义的方法/函数
FunctionsBuilder类附带了一堆现成的方法/函数供您使用，例如 sum() , count() , concat() , dateDiff() , now()等。您可以找到支持的函数的完整列表以及如何使用它们的示例in the Cookbook和 the API docs .

只需调用它们就可以构建任意函数
FunctionsBuilder类使用魔术方法__call处理程序来构建任意 SQL 函数表达式，所以如果你的函数没有现成的方法，你可以“调用”你的 SQL 函数:

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

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

这应该很容易解释，魔术方法名称是 SQL 函数名称，传递的数组包含应该传递给函数的参数，在这种情况下，第一个和最后一个参数被定义为分别被视为标识符作为文字，因此两者都直接插入到查询中，即不是作为将被转义的绑定(bind)参数!

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

生成的编译 SQL 将如下所示:

substring_index(name, :c0, -1)

最终将被执行为

substring_index(name, ' ', -1) 

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

当处理非硬编码的数据时，即动态的，或可能发生变化的数据，确保在必要时在第二个参数中定义正确的类型转换/转义，如 integer , datetime等。为了使其正常工作，您必须对列名值使用标识符表达式，否则在使用 'xyz' => 'identifier' 时第二个参数将被忽略。句法:

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

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

你甚至可以使用原始表达式

在您的情况下，您传递安全的硬编码值，这些值不需要作为绑定(bind)参数插入到查询中， SUBSTRING_INDEX不是 CakePHP 附带的任何方言所涵盖的功能，您甚至可以使用原始查询代替 - 但是您将失去在自定义方言中转换表达式的能力，并且自动标识符引用也不会应用任何更多，所以只有在你知道自己在做什么的情况下才这样做!

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

也可以看看

Cookbook > Database Access & ORM > Query Builder > Using SQL functions

API > \Cake\Database\Query::newExpr()