如何使用 Laravel 查询生成器在一个选择中获取预订集合

Question

有了以下代码，我想根据单位选择所有预订

$unitsQuery = DB::table('units as u')

        ->leftJoin('reservations as r', function ($join) use($fromDate,$toDate) {

            $join->on('u.id', '=', 'r.unit_id')

                ->where('r.team_id', auth()->user()->current_team_id)

                ->where('r.date_in ', '>=' , $fromDate)

                ->where('r.date_out ', '<=' , $toDate)

                ->whereNull('r.checked_out')

                ->whereNull('r.deleted_at')

                ->where('date_out', '!=', $fromDate)

                ->where('r.status' , '!=' , 'canceled')

                ->leftJoin('customer as c','r.customer_id' , '=' ,'c.id')

                ->leftJoin('wallets as w', function ($join) {

                    $join->on('r.id', '=', 'w.holder_id')

                        ->where('w.holder_type' , 'App\Reservation');

                });

        })

        ->select('u.id as uid',

            'u.name as uname',

            'u.team_id as utid',

            'u.unit_number as unum',

            'u.status as ustatus',

            'u.sunday_day_price as sunday_price',

            'u.monday_day_price as monday_price',

            'u.tuesday_day_price as tuesday_price',

            'u.wednesday_day_price as wednesday_price',

            'u.thursday_day_price as thursday_price',

            'u.friday_day_price as friday_price',

            'u.saturday_day_price as saturday_price',

            'u.month_price as month_price',

       

        )

        ->where('u.team_id' , auth()->user()->current_team_id)

        ->where('u.enabled' , 1)

        ->whereNull('u.deleted_at')

        ->orderBy('u.unit_number' , 'asc')

        ->groupBy(['u.id'])

        ->get();

我想要实现的是根据 $fromDate 和 $toDate 获取我的所有房间以及该房间下的所有预订，如何在一个选择中实现这一点以获得类似的结果

   {

      "u.id":1,

      "reservations":[

         {

            "id":1,

            "price":200

         },

         {

            "id":2,

            "price":500

         }

      ]

   },

Answer 1

如果您使用 Unit 作为雄辩模型，请执行正常查询。您可以使用 with 来加载关系，如果您在 API 控制器中返回这些单位，则关系将被转换。基本上你的逻辑但是做到了雄辩的风格，可能有些SQL命名可能有错误，但基本思想是存在的。$units = Unit::query()&nbsp; &nbsp; ->leftJoin('reservations as r', function ($join) use($fromDate,$toDate) {&nbsp; &nbsp; &nbsp; &nbsp; $join->on('units.id', '=', 'r.unit_id')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->where('r.team_id', auth()->user()->current_team_id)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->where('r.date_in ', '>=' , $fromDate)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->where('r.date_out ', '<=' , $toDate)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->whereNull('r.checked_out')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->whereNull('r.deleted_at')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->where('date_out', '!=', $fromDate)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->where('r.status' , '!=' , 'canceled')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->leftJoin('customer as c','r.customer_id' , '=' ,'c.id')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->leftJoin('wallets as w', function ($join) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $join->on('r.id', '=', 'w.holder_id')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->where('w.holder_type' , 'App\Reservation');&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; })&nbsp; &nbsp; ->select(&nbsp; &nbsp; &nbsp; &nbsp; 'units.id as uid',&nbsp; &nbsp; &nbsp; &nbsp; 'units.name as uname',&nbsp; &nbsp; &nbsp; &nbsp; 'units.team_id as utid',&nbsp; &nbsp; &nbsp; &nbsp; 'units.unit_number as unum',&nbsp; &nbsp; &nbsp; &nbsp; 'units.status as ustatus',&nbsp; &nbsp; &nbsp; &nbsp; 'units.sunday_day_price as sunday_price',&nbsp; &nbsp; &nbsp; &nbsp; 'units.monday_day_price as monday_price',&nbsp; &nbsp; &nbsp; &nbsp; 'units.tuesday_day_price as tuesday_price',&nbsp; &nbsp; &nbsp; &nbsp; 'units.wednesday_day_price as wednesday_price',&nbsp; &nbsp; &nbsp; &nbsp; 'units.thursday_day_price as thursday_price',&nbsp; &nbsp; &nbsp; &nbsp; 'units.friday_day_price as friday_price',&nbsp; &nbsp; &nbsp; &nbsp; 'units.saturday_day_price as saturday_price',&nbsp; &nbsp; &nbsp; &nbsp; 'units.month_price as month_price',&nbsp; &nbsp; )&nbsp; &nbsp; ->where('units.team_id' , auth()->user()->current_team_id)&nbsp; &nbsp; ->where('units.enabled' , 1)&nbsp; &nbsp; ->whereNull('units.deleted_at')&nbsp; &nbsp; ->orderBy('units.unit_number' , 'asc')&nbsp; &nbsp; ->groupBy(['units.id'])&nbsp; &nbsp; ->with('reservations')&nbsp; &nbsp; ->get();return $units;