doutandusegang2961
2018-04-12 20:08
采纳率: 0%
浏览 281
已采纳

laravel 5.5 group by不起作用

I'm trying to get some data but I just get this error

SQLSTATE[42000]: Syntax error or access violation: 1055 'ms_mascotas.ms_razas.id' isn't in GROUP BY (SQL: select ms_razas.id, ms_razas.nombre, ms_mascotas.raza_id, ms_mascotas.id from ms_mascotas inner join ms_razas on ms_razas.id = ms_mascotas.raza_id where ms_razas.tipo_animal_id = 1 group by ms_mascotas.raza_id)"

my query is this

$data=Mascota::select("ms_razas.id","ms_razas.nombre","ms_mascotas.raza_id","ms_mascotas.id")
       ->join("ms_razas","ms_razas.id","=","ms_mascotas.raza_id")
       ->where("ms_razas.tipo_animal_id",$id)
       ->groupBy("ms_mascotas.raza_id")
       ->get();

I've been reading about this error and it is related with the strict mode in the database file, strict is set to false by default, what should I do?

图片转代码服务由CSDN问答提供 功能建议

我正在尝试获取一些数据,但我只是得到了这个错误

SQLSTATE [42000]:语法错误或访问冲突:1055'ms_mascotas.ms_razas.id'不在GROUP BY中(SQL:select ms_razas.id ms_razas .nombre ms_mascotas.raza_id ms_mascotas.id 来自 ms_mascotas ms_razas ms_razas.id = ms_mascotas.raza_id 其中 ms_razas.tipo_animal_id = 1 group by ms_mascotas.raza_id )“

我的查询是这个

  $ data = Mascota :: select(“ms_razas.id”,“ms_razas.nombre”  ,“ms_mascotas.raza_id”,“ms_mascotas.id”)
  - > join(“ms_razas”,“ms_razas.id”,“=”,“ms_mascotas.raza_id”)
  - > where(“ms_razas。  tipo_animal_id“,$ id)
  - > groupBy(”ms_mascotas.raza_id“)
  - > get(); 
   
 
 

我一直在阅读 关于这个错误,它与s有关 数据库文件中的trict模式,默认情况下strict设置为false,我该怎么办?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • duanliaolan6178 2018-04-13 04:49
    已采纳

    go to your config\database.php and change the setting the database that you use

    'strict' => false
    
    已采纳该答案
    打赏 评论
  • dtlh12053 2018-04-12 20:31

    Everything you select you must add in the group by, here is an example

     Mascota::select("ms_razas.id","ms_razas.nombre","ms_mascotas.raza_id","ms_mascotas.id")
           ->join("ms_razas","ms_razas.id","=","ms_mascotas.raza_id")
           ->where("ms_razas.tipo_animal_id",$id)
           ->groupBy("ms_razas.id","ms_razas.nombre","ms_mascotas.raza_id","ms_mascotas.id")
           ->get();
    

    You need to have a full group by:

    SELECT `name`, `type`, `language`, `code` 
    FROM `users` 
    WHERE `verified` = '1' 
    GROUP BY `name`, `type`, `language`, `code` 
    ORDER BY `count` DESC LIMIT 0, 25
    

    SQL92 requires that all columns (except aggregates) in the select clause is part of the group by clause. SQL99 loosens this restriction a bit and states that all columns in the select clause must be functionally dependent of the group by clause. MySQL by default allows for partial group by and this may produce non-deterministic answers, example:

    create table t (x int, y int);
    insert into t (x,y) values (1,1),(1,2),(1,3);
    select x,y from t group by x;
    +------+------+
    | x    | y    |
    +------+------+
    |    1 |    1 |
    +------+------+
    

    I.e. A random y is select for the group x. One can prevent this behavior by setting @@sql_mode:

    set @@sql_mode='ONLY_FULL_GROUP_BY';
    select x,y from t group by x; 
    ERROR 1055 (42000): 'test.t.y' isn't in GROUP BY
    
    打赏 评论

相关推荐 更多相似问题