Geek Answers Handbook

How to generate SQL function calls using CakePHP query builder?

I have a fullname column for authors and would like to extract the last name into another column. I am doing this with the following raw SQL:

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

Conclusion:

enter image description here

In the "Using SQL Functions" section in the Cookbook, it says:

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

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

+4
source share
1 answer

The function constructor comes with predefined methods / functions

FunctionsBuilder / , sum(), count(), concat(), dateDiff(), now() .. , 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] .

( , , '" "'), , , . , / .

SQL :

substring_index((name), :c0, -1)

, ,

substring_index((name), ' ', -1)

-

, ,

, , , , / , integer, datetime, .. , , 'xyz' => 'identifier'.

use Cake\Database\Expression\IdentifierExpression;

// ...

substring_index(
    [
        new IdentifierExpression('title'),
        ' ',
        $value,
    ],
    [
        null,
        'string',
        'integer'
    ]
)

, , , null.

-

, , , , SUBSTRING_INDEX , , CakePHP, , .

, , , ,

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

-

.

+2

More articles:


All Articles