Laravel模型查询错误:"Illuminate \ Database \ QueryException:SQLSTATE [23000]:违反完整性约束" [英] Laravel Model Query Error : "Illuminate\Database\QueryException: SQLSTATE[23000]: Integrity constraint violation"

问题描述

我尝试加入2个表，但出现此错误:

I try to join 2 tables, but get this error :

Illuminate \ Database \ QueryException: SQLSTATE [23000]:违反完整性约束:1052 where子句中的"user_id"列不明确(SQL:选择sadaqah_history.total，sadaqah_history .foundation_donate_id来自sadaqah_history上的dzikir_counter内部联接sadaqah_history.user_id = dzikir_counter.user_id其中user_id = 25和DATE_FORMAT(dzikir_date，'％Y-％m-％d' )在第664行上的/var/www/backend/z/vendor/laravel/framework/src/Illuminate/Database/Connection.php文件中的'2019-07-01'和'2019-07-31')之间>

Illuminate\Database\QueryException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'user_id' in where clause is ambiguous (SQL: select sadaqah_history.total, sadaqah_history.foundation_donate_id from dzikir_counter inner join sadaqah_history on sadaqah_history.user_id = dzikir_counter.user_id where user_id = 25 and DATE_FORMAT(dzikir_date, '%Y-%m-%d') BETWEEN '2019-07-01' AND '2019-07-31') in file /var/www/backend/z/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 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查询:

What I want to achieve is SQL Query identic to this :

SELECT ，dzikir_counter.user_id，dzikir_counter.dzikir_date，sadaqah_history.sadaqah_date，dzikir_counter.dzikir_total，sadaqah_history.point_total，sadaqah_historytoate.sadaqah_history.total. FROM dzikir_counter 内部联接 sadaqah_history ON dzikir_counter.user_id = sadaqah_history.user_id AND dzikir_counter.dzikir_date = sadaqah_history.sadaqah_date
ORDER BY .sadaqah_history.foundation_donate_id DESC

SELECT 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 FROM dzikir_counter inner join sadaqah_history ON dzikir_counter.user_id = sadaqah_history.user_id AND dzikir_counter.dzikir_date = sadaqah_history.sadaqah_date
ORDER BY sadaqah_history.foundation_donate_id DESC

推荐答案

替换

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

使用

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

您得到的错误意味着dzikir_counter和sadaqah_history表中都存在user_id列.在这种情况下，您应该指定应该在哪个表上执行WHERE.

The error you get means that the user_id column exists in both the dzikir_counter and the sadaqah_history tables. In that case you should specify which table the WHERE should be executed.

这篇关于Laravel模型查询错误:"Illuminate \ Database \ QueryException:SQLSTATE [23000]:违反完整性约束"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！