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