我有一个简单的Education模型包含以下字段:
e_id // Primary key
user_id //foreign key to link to User model
field
grade
university
country_education
city_education
date_of_graduation这是我的模型结构:
class Education extends Model
{
public $primaryKey = 'e_id';
public $timestamps = false;
protected $table = 'educations';
protected $fillable = ['user_id', 'field', 'grade', 'university', 'country_education', 'city_education', 'date_of_graduation'];
public function user ()
{
return $this->belongsTo('App\User', 'user_id', 'user_id');
}
}现在,我希望基于user_id字段选择不同的行。对此,我写了如下:
$educations = Education::select(['e_id', 'user_id', 'field', 'grade'])->groupBy(['user_id']);但下面出现了错误:
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'lms_forms.educations.e_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `e_id`, `user_id`, `field`, `grade` from `educations` group by `user_id` limit 10 offset 0)然后我将e_id主键添加到groupBy(['user_id'])中:
$educations = Education::select(['e_id', 'user_id', 'field', 'grade'])->groupBy(['user_id','e_id']);查询运行,但返回所有记录,而不考虑user_id的区别。
什么是问题,我能做什么?
发布于 2016-11-14 09:25:07
您已经打开了ONLY_FULL_GROUP_BY模式(默认情况下,它被设置为on MySql 5.7+)。
这是对最新mysql版本的改进,以避免从查询结果中删除行。不要用PK对其进行分组,而是尝试将其与其他列进行分组以返回正确的结果。
否则,如果您只想继续执行结果,则禁用此模式。
见- BY
发布于 2018-07-01 23:14:53
试试这个..。在文件夹config => database.php中确保mysql 严格的是false,如下所示
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8',
'collation' => 'utf8_general_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
],如果严格为真,则通过在cmd中运行以下命令,使其为false,然后清除配置现金。
配置:清除
https://stackoverflow.com/questions/40585265
复制相似问题