如何在Laravel Eloquent查询结果中添加带NULL值的新列？

你在Laravel里执行关联查询时，想加一个值为NULL的organization_id列，但直接写'null as organization_id'会触发PostgreSQL报错SQLSTATE[42703]: Undefined column: 7 ERROR: column "null" does not exist，原因是Laravel把null当成了列名，而不是字面量值。

下面是几种可行的解决方法：

• 方法一：用DB::raw()包裹NULL表达式

use Illuminate\Support\Facades\DB;

$user = User::select(
    'users.id as id',
    'users.personnel_id as personnel_id',
    'agency_users.agency_id as agency_id',
    DB::raw('null as organization_id'),
)
->join('agency_users', 'agency_users.user_id', 'users.id')
->where('users.id', Auth::id())
->first();

• 方法二：使用selectRaw()方法

$user = User::selectRaw(
    'users.id as id, users.personnel_id as personnel_id, agency_users.agency_id as agency_id, null as organization_id'
)
->join('agency_users', 'agency_users.user_id', 'users.id')
->where('users.id', Auth::id())
->first();

• 方法三：在select数组中用闭包定义表达式

$user = User::select([
    'users.id as id',
    'users.personnel_id as personnel_id',
    'agency_users.agency_id as agency_id',
    function ($query) {
        $query->selectRaw('null')->as('organization_id');
    }
])
->join('agency_users', 'agency_users.user_id', 'users.id')
->where('users.id', Auth::id())
->first();

原理很简单：Laravel查询构造器默认会把select里的字符串参数当作列名或表引用，所以直接写'null as organization_id'时，它会去查名为null的列，自然找不到。用DB::raw()或selectRaw()能明确告诉Laravel，这是一段原生SQL表达式，不需要解析成列名。

内容的提问来源于stack exchange，提问作者Bahram Golshan