英文:
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) {...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论