Laravel 10 DB:raw错误地在SQL函数周围添加反引号

huangapple go评论94阅读模式
英文:

Laravel 10 DB:raw incorrectly putting backticks around sql functions

问题

在Laravel 9中,我们有一些原始的SQL语句,例如以下代码:

...
->whereIn(DB:raw('concat(a_col, b_col)'), function ($query) {
...

为了与Laravel 10兼容,我们将这些代码更改为使用getValue方法:

...
->whereIn(DB::raw('concat(a_col, b_col)')->getValue(DB::getQueryGrammar()), function ($query) {
...

这在大多数情况下都有效,但是对于使用concat等函数的SQL语句,它会产生带有反引号的SQL,例如:

...
AND `concat(a_col, b_col)` IN (
...

这会导致错误,因为反引号意味着整个语句是一个列:Column not found: 1054 Unknown column 'concat(a_col, b_col)' in 'IN/ALL/ANY subquery'

以下是一个完整的Laravel 9查询构建示例,以供参考:

$query = DB::table('table_a')
->join('table_b', 'table_b.id', '=', 'table_a.col_c')
->select([
    'table_b.c_id',
    DB::raw('COUNT(DISTINCT(col_c)) AS any_count'),
    DB::raw('COUNT(CASE WHEN col_b = 1 THEN 1 END) AS one_count'),
    DB::raw('COUNT(CASE WHEN col_b = 2 THEN 1 END) AS two_count'),
])
->where('col_d', '=', ColD::class)
->where('col_e', '=', true)
->whereIn(DB::raw('concat(col_a, col_b, col_c)'), function ($query) {
    $query->from('table_a as latest_table_a')
          ->selectRaw('concat(max(col_a), col_b, col_c) as max_unique_cols')
          ->whereColumn('col_d', 'table_a.col_d')
          ->whereColumn('col_c', 'table_a.col_c')
          ->groupBy('col_d', 'col_c', 'col_b');
})
->groupBy('table_b.c_id');
英文:

In Laravel 9 we had raw sql lines such as the following:

...
->whereIn(DB:raw('concat(a_col, b_col)'), function ($query) {
...

In order to be compatible with Laravel 10 we changed such lines to use the getValue method:

...
->whereIn(DB::raw('concat(a_col, b_col)')->getValue(DB::getQueryGrammar()), function ($query) {
...

This worked in most places, however for sql that used functions like concat above it produces sql with backticks e.g:

...
AND `concat(a_col, b_col)` IN (
...

This produces an error as the backticks imply the whole statement is a column: Column not found: 1054 Unknown column 'concat(a_col, b_col)' in 'IN/ALL/ANY subquery'

Here's a Laravel 9 full query build example for context:

$query = DB::table('table_a')
->join('table_b', 'table_b.id', '=', 'table_a.col_c')
->select([
    'table_b.c_id',
    DB::raw('COUNT(DISTINCT(col_c)) AS any_count'),
    DB::raw('COUNT(CASE WHEN col_b = 1 THEN 1 END) AS one_count'),
    DB::raw('COUNT(CASE WHEN col_b = 2 THEN 1 END) AS two_count'),
])
->where('col_d', '=', ColD::class)
->where('col_e', '=', true)
->whereIn(DB::raw('concat(col_a, col_b, col_c)'), function ($query) {
    $query->from('table_a as latest_table_a')
          ->selectRaw('concat(max(col_a), col_b, col_c) as max_unique_cols')
          ->whereColumn('col_d', 'table_a.col_d')
          ->whereColumn('col_c', 'table_a.col_c')
          ->groupBy('col_d', 'col_c', 'col_b');
})
->groupBy('table_b.c_id');

答案1

得分: 0

现在发现,只要你在Laravel 10的查询构建器内部,DB::raw()将像以前一样处理。所以我不需要改变任何东西!

也就是说,保持代码不变:

...
->whereIn(DB:raw('concat(a_col, b_col)'), function ($query) {
...
英文:

Have now found out that as long as you are inside a query builder Laravel 10 will handle DB::raw() as before. So I didn't need to change anything!

i.e keep the code like this:

...
->whereIn(DB:raw('concat(a_col, b_col)'), function ($query) {
...

huangapple
  • 本文由 发表于 2023年8月9日 01:31:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/76861943.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定