如何使用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函数下，菜谱说：

Under "Using SQL Functions" the Cookbook says:

除了上述函数外，func（）方法还可用于创建任何通用SQL函数，例如year，date_format，转换等。

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'=>时，第二个参数将被忽略。 'identifier'语法：

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

类型将通过数字索引进行匹配，第一个将被忽略，因为它是一个表达式，因此只传递 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> \蛋糕\数据库\Query :: newExpr（）



See also

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

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