Laravel模型查询错误：“Illumb\Database\QueryException:SQLSTATE[23000]：完整性约束冲突”

Question

我尝试联接2个表，但出现以下错误：

照明\数据库\查询异常： SQLSTATE[23000]：完整性约束违反： 1052列'user_id'中的子句是含糊不清的（SQL：选择sadaqah_history。总计，sadaqah_history。foundation_donate_idfromdzikir_counter内连接sadaqah_history在sadaqah_history。user_id=dzikir_counter。user_id其中user_id=25和DATE_FORMAT（dzikir_date，'%Y-%m-%d'）之间'2019-07-01'AND'2019-07-31'）在文件 /var/www/backend/z/vendor/laravel/framework/src/Illuminate/Database/Connection.php行664

$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();

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'
];

    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_计数器。用户id，dzikir_计数器。日月星辰，萨达卡历史。sadaqah_date，dzikir_柜台。dzikir_total，sadaqah_历史。总分，萨达卡历史。总计，sadaqah_历史。基金会捐赠的身份证来自于齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐齐。用户id=sadaqah\u历史记录。用户id和dzikir_计数器。dzikir_date=萨达喀历史。sadaqah_日期
订单依据sadaqah_历史记录<代码>基金会捐赠idDESC

Answer 1

替换

dzikicounter:：where（'user_id'，$user-

具有

::dzikir_counter.user_id

您得到的错误意味着user\u id列同时存在于dzikir\u计数器和sadaqah\u历史表中。在这种情况下，您应该指定应该在何处执行该表。