拉拉维尔模型查询错误："IlluminateDatabaseQueryException: SQLSTATE[23000]: Integrity constraint violation"

Question

我尝试连接 2 个表，但收到此错误：

Illuminate\Database\QueryException：SQLSTATE[23000]： 完整性约束冲突： 1052其中子句中的列"user_id"不明确(SQL： 选择sadaqah_history.total，sadaqah_history.foundation_donate_id从dzikir_counter内部连接sadaqah_historysadaqah_history。user_id=dzikir_counter.user_id其中user_id= 25 和 DATE_FORMAT(dzikir_date， '%Y-%m-%d') 在文件/var/www/backend/z/vendor/laravel/framework/src/Illuminate/Database/Connection.php 第 664 行

我的 API 控制器代码 ：

$startDate = Carbon::parse(new Carbon('first day of this month'))->toDateString();
$endDate = Carbon::parse(new Carbon('last day of this month'))->toDateString();
$models = DzikirCounter::where('user_id', $user->id)
->whereRaw("DATE_FORMAT(dzikir_date, '%Y-%m-%d') BETWEEN '$startDate' AND '$endDate'")
->join('sadaqah_history', 'sadaqah_history.user_id', '=', 'dzikir_counter.user_id')
->get(['sadaqah_history.total', 'sadaqah_history.foundation_donate_id'])              
->actived()
->orderBy('dzikir_date', 'desc')
->get();

模型 1 (Dzikir_Counter) ：

protected $table = 'dzikir_counter';
protected $fillable = [
'user_id',
'counter',
'dzikir_total',
'dzikir_date',
'total',
'dzikir_detail',
'status',
'deleted_at',
'created_at',
'updated_at',
'created_by',
'updated_by',
];
protected $appends = [
'dzikir_details'
];
protected $hidden = [
'dzikir_detail',
'user_id',
'created_by',
'updated_by',
'deleted_at'
];
protected $casts = [
'counter' => 'int',
'dzikir_total' => 'int',
'status' => 'int',
'user_id' => 'int'
];

模型 2 (Sadaqah_History) ：

protected $table = 'sadaqah_history';
protected $fillable = [
'user_id',
'name',
'point_total',
'total',
'sadaqah_date',
'status',
'is_dzikir',
'foundation_donate_id',
'created_at',
'updated_at',
'created_by',
'updated_by',
];
protected $hidden = [
'id',
'user_id',
'name',
'point_total',
'created_at',
'updated_at',
'created_by',
'updated_by',        
'foundation_donate_id',
'created_by',
'updated_by'
];
protected $casts = [
'status' => 'int',
'is_dzikir' => 'int',
'point_total' => 'int',
'total' => 'int',
'user_id' => 'int',
'foundation_donate_id' => 'int',
];

我想要实现的是与此相同的SQL查询：

选择 dzikir_counter.user_id， dzikir_counter.dzikir_date，

sadaqah_history.sadaqah_date， dzikir_counter.dzikir_total， sadaqah_history.point_total， sadaqah_history.total， sadaqah_history.foundation_donate_id从dzikir_counter内部连接sadaqah_historyONdzikir_counter.user_id = sadaqah_history.user_id AND dzikir_counter.dzikir_date = sadaqah_history.sadaqah_date
ORDER BYsadaqah_history.foundation_donate_idDESC

Answer 1

替换

DzikirCounter::where('user_id', $user->id)

跟

DzikirCounter::where('dzikir_counter.user_id', $user->id)

您得到的错误意味着user_id列同时存在于dzikir_counter表和sadaqah_history表中。在这种情况下，您应该指定应执行 WHERE 的表。