时间戳

首页 > 解决方案 > 使用 laravel eloquent 合并/连接数据行

laravel - 使用 laravel eloquent 合并/连接数据行

问题描述

我通过以下方式从严重表中检索数据：

$data = Accommodation::join("hotels", "accommodations.hotel_id", "=", "hotels.id")
        ->join("room_types", "accommodations.room_type", "=", "room_types.id")
        ->join("country", "accommodations.country_id", "=", "country.id")
        ->join("accommodation_accreditation", "accommodations.id", "=", "accommodation_accreditation.accommodation_id")
        ->join("accreditation", 'accreditation.id', "=", "accommodation_accreditation.accreditation_id")
        ->select('hotels.name as Hotel' ,'room_types.type as Room', 'price_per_night as Price per Night', 'check_in as Check In', 'check_out as Check Out', 'nights as Nights', 'sub_total as Sub Total', 'country.name as Country', 'accreditation.full_name as Occupants')
        ->get()->toArray();

我收到一个房间里每个人的一排。

          Room           Price per Night    Check In    Check Out    Nights   Sub Total   Country       Occupants      
 ---------------------- ----------------- ------------ ------------ -------- ----------- --------- ------------------- 
  Twin/Double standart               160   2019-04-10   2019-04-15        5         800   UKR       Vlad Timochenko  
  Twin/Double standart               160   2019-04-10   2019-04-15        5         800   UKR       Selena Viachenko

我需要连接住户的姓名并将它们排成一行，如下所示：

          Room           Price per Night    Check In    Check Out    Nights   Sub Total   Country               Occupants               
 ---------------------- ----------------- ------------ ------------ -------- ----------- --------- ------------------------------------ 
  Twin/Double standart               160   2019-04-10   2019-04-15        5         800   UKR       Vlada Nikolchenko, Olena Diachenko

这是我有问题的表的架构

在此处输入图像描述

希望很清楚。如果您能提供帮助，将非常高兴。

标签： laraveleloquent

解决方案

老实说，我从不同的地方为我的案例找到了完整的解决方案。

正如山姆所建议的那样，我使用了GROUP_CONCAT. 我的代码看起来像：

$data = Accommodation::join("hotels", "accommodations.hotel_id", "=", "hotels.id")
        ->join("room_types", "accommodations.room_type", "=", "room_types.id")
        ->join("country", "accommodations.country_id", "=", "country.id")
        ->join("accommodation_accreditation", "accommodations.id", "=", "accommodation_accreditation.accommodation_id")
        ->join("accreditation", 'accreditation.id', "=", "accommodation_accreditation.accreditation_id")
        ->select('hotels.name as Hotel' ,'room_types.type as Room', 'price_per_night as Price per Night', 'check_in as Check In', 'check_out as Check Out', 'nights as Nights', 'sub_total as Sub Total', 'country.name as Country', DB:raw("GROUP_CONCAT('accreditation.full_name SEPRATOR ',') as `Occupants`"))
        ->groupBy('accommodation.id')
        ->get()->toArray();

我必须小心的另一件事是对 Occupants 标签使用反引号，否则 SEPARATOR 无法正常工作。

还有其他类似的线程，所以这是重复的。

Group_concat - laravel 雄辩 的如何在 laravel 中使用 GROUP_CONCAT

推荐阅读