mysql - 如何在 laravel 中将这个复杂的 mysql 查询作为原始查询运行

Question

我有一个 mysql 查询，当我在 phpmyadmin 中运行这个查询时工作得很好。原始的 sql 查询是

SELECT IF(COUNT(u.id) > 1
     , GROUP_CONCAT(CONCAT(a.amenity_name, '_' ,a.id)), a.amenity_name) as m_concat
     , u.id as unit_id
     , u.building_id
     , (uav.id) as uav_id
     , a.* 
  FROM amenities a 
  JOIN amenity_values av 
    ON a.id = av.amenity_id 
  JOIN units_amenities_values uav 
    ON av.id = uav.amenity_value_id 
  JOIN units u ON u.id = uav.unit_id 
 where a.category_id = 370 
   AND a.property_id = 82 
   AND u.building_id = 1265 
 group 
    by u.id 
 order 
    by u.id asc

但是，当我尝试在 laravel 中将确切的查询作为原始查询运行时，它会抛出错误。我正在尝试将其运行为：

$statement = DB::select(DB::raw("SELECT IF(COUNT(u.id) > 1, GROUP_CONCAT(CONCAT(a.amenity_name, '_' ,a.id)), a.amenity_name) as m_concat, u.id as unit_id, u.building_id, (uav.id) as uav_id, a.* FROM amenities a INNER JOIN amenity_values av ON a.id = av.amenity_id INNER JOIN units_amenities_values uav ON av.id = uav.amenity_value_id INNER JOIN units u ON u.id = uav.unit_id where a.category_id = '370' AND a.property_id = '82' AND u.building_id = '1265' group by u.id order by u.id asc"));

在运行上面的确切语句时，它会引发以下错误：

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'amenity_db_prod.a.amenity_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: SELECT IF(COUNT(u.id) > 1, GROUP_CONCAT(CONCAT(a.amenity_name, '_' ,a.id)), a.amenity_name) as m_concat, u.id as unit_id, u.building_id, (uav.id) as uav_id, a.* FROM amenities a INNER JOIN amenity_values av ON a.id = av.amenity_id INNER JOIN units_amenities_values uav ON av.id = uav.amenity_value_id INNER JOIN units u ON u.id = uav.unit_id where a.category_id = '370' AND a.property_id = '82' AND u.building_id = '1265' group by u.id order by u.id asc)

标签： mysqllaravel