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

问题描述

我有一个作者的全名列，并希望将姓氏提取到另一列。我使用以下原始SQL：

  SELECT name，
 SUBSTRING_INDEX（`name`，'' 1）AS`surname` 
 FROM qr.authors; 
  

输出：

在使用SQL函数下，Cookbook说：

除了上面的函数，func（）方法可以用来创建任何通用的SQL函数，如year，date_format，转换等。

但是如何通过func（）方法创建这个SUBSTRING_INDEX函数，以便我可以使用它与CakePHP查询构建器？

解决方案

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

FunctionsBuilder 类附带了一些现成的方法/函数供您使用，例如 sum（）， count（）， concat（）， dateDiff（）， now（）等。您可以找到支持函数的完整列表，以及如何使用它们的示例 在Cookbook 和 API文档 。

任意函数可以通过调用它们来构建

FunctionsBuilder 类使用魔法 __ call 处理程序来构建任意的SQL函数表达式，所以如果没有为你的函数准备好的方法，你只需调用你的SQL函数

  $ query = $ this-> SomeTable-> find（） 
 
 $ func = $ query-> func（） - > substring_index（[
'name'=>'identifier'，
''，
 -1 =>'literal'
]）; 
 $ query-> select（[/ * ... * /，'surname'=> $ func]）; 
  

这应该主要是自我解释，魔法方法名称是SQL函数名称，数组包含应传递给函数的参数，在这种情况下，第一个和最后一个参数被定义为分别作为文字处理的标识符，因此两者都直接插入查询，即不作为绑定参数

标识符将另外受到可能的自动标识符引用，即 name 将转换为例如`name` ，name或 [name] 取决于使用的数据库驱动程序。

第二个参数也可以是一个文字（通过传递例如' ），我只是没有设置为一个例如puposes。

结果编译的SQL将类似于

  substring_index（（name），：c0，-1）
  

$ b b

并最终执行为

  substring_index（（name），''，-1）
  
 
 
 
  -  
 
 
 
处理用户输入
 
 
 
使用用户输入时，如果需要，请确保在第二个参数中定义适当的类型转换/转义，例如 integer   datetime 等。为了正常工作，您必须使用标识符表达式为列名值，否则第二个参数将被忽略使用'xyz'=> '
 使用Cake \Database \Expression\IdentifierExpression; 
 
 // ... 
 
 substring_index（
 [
 new IdentifierExpression（'title'），
''，
 $ userSubmittedValue，
]，
 [
 null，
'string'，
'integer'
] 
）
  
类型将通过数字索引匹配，第一个将被忽略，因为它是一个表达式，因此只传递 null 。
 
 
 
  -  
 
 
 
您甚至可以使用原始表达式
 
 
 
在您的情况下，您传递的安全的硬编码值不需要作为绑定参数插入到查询中， code> SUBSTRING_INDEX 不是CakePHP附带的任何方言所覆盖的函数，您甚至可以使用原始查询 - 在这种情况下，您将放弃自动标识符引用。
 
 
 
所以除非你需要自动引用，或者你使用自定义方言，并且需要翻译的函数，你甚至可以使用一个原始表达式
  $ query-> newExpr（'SUBSTRING_INDEX（`name`，，-1）'）
  
  -  
 
 
 
另请参阅
 
 
 
 
 
   Cookbook>数据库访问& ORM>查询生成器>使用SQL函数  
 
 
   API> \Cake\Database\Query :: newExpr（）  
 
 
 
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?
解决方案
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 explantory, 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 puposes. Not doing so will cause the value to be casted/escaped as a string.

The resulting compiled SQL will look something like
substring_index((name), :c0, -1)
and will finally be executed as
substring_index((name), ' ', -1) 
-

Handling user input

When working with user input, 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.
use Cake\Database\Expression\IdentifierExpression;

// ...

substring_index(
    [
        new IdentifierExpression('title'),
        ' ',
        $userSubmittedValue,
    ],
    [
        null,
        'string',
        '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, hardcoded 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 loose automatic identifier quoting in that case though.

So unless you need auto quoting, or you are using custom dialects and need the function to be translated, you could even use a raw expression like
$query->newExpr('SUBSTRING_INDEX(`name`, "", -1)')
-

See also


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


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