php - 如何从 Laravel 中的 2 个连接中删除重复项

Question

我正在使用 spatie laravel 查询构建器为我的项目制作一些过滤器，我的代码如下:

public function scopeFilter()
    {
        $data = QueryBuilder::for(Accommodation::class)
            ->allowedFilters([
                AllowedFilter::scope('bed_count'),
                AllowedFilter::scope('filter_price'),
                AllowedFilter::scope('filter_date'),
                AllowedFilter::scope('discounts'),
                AllowedFilter::scope('name'),
                AllowedFilter::exact('grade_stars'),
                AllowedFilter::exact('city_id'),
                AllowedFilter::exact('is_recommended'),
                AllowedFilter::exact('accommodation_type_id'),
                AllowedFilter::scope('accommodation_facility')
//                'name',
            ])
            ->allowedAppends(['cheapestroom'])
            ->allowedIncludes(['gallery','city','accommodationRooms','accommodationRooms.roomPricingHistorySearch','discounts','prices'])
            ->allowedSorts([
                AllowedSort::custom('discount', new DiscountSort() ,'amount'),
                AllowedSort::custom('price', new PriceSort() ,'price'),
            ])
            ->paginate(12);
        return FilterResource::collection($data);

现在我有 1 个排序和 2 个过滤器，这对我来说非常重要，这是它们的代码

排序

  $data = $query->join('accommodation_rooms as cr', 'accommodations.id', '=', 'cr.accommodation_id')
            ->join('room_pricing_histories as dr','cr.id', '=', 'room_pricing_histories.accommodation_room_id')
//            ->select('cr.id')
            ->orderBy('dr.sales_price', 'desc')
            ->select('dr.sales_price', 'accommodations.*')
            ->groupBy('dr.sales_price','accommodations.id');
        $direction = $descending ? 'DESC' : 'ASC';

        return $data;

第一个过滤器被写为作用域

 public function scopeFilterPrice(Builder $query, $start_price, $end_price): Builder
    {
        $data = $query->leftjoin('accommodation_rooms as ar','ar.id','=','accommodations.id')
            ->leftjoin('room_pricing_histories','room_pricing_histories.id','=','ar.id')
            ->select('accommodations.*')
            ->where('room_pricing_histories.sales_price', '>', $start_price)
            ->where('room_pricing_histories.sales_price', '<', $end_price);
        return $data;
    }

第二个过滤器也作为范围

public function scopeFilterDate(Builder $query,$from_date,$to_date): Builder{

        $data = $query->leftjoin('accommodation_rooms as br','br.id','=','accommodations.id')
            ->Join('room_capacity_histories','room_capacity_histories.id','=','br.id')
            ->select('accommodations.*')
            ->whereDate('room_capacity_histories.from_date', '>', $from_date)
            ->whereDate('room_capacity_histories.to_date', '<', $to_date);

        return $data;

但是无论我使用什么 groupby 或其他东西，这都会给我带来重复的数据，我想知道我是否有可能删除资源或其他地方的重复项。

注意

我确实使用了->unique()，但它不再具有分页结构，这给我带来了问题，所以我首先想要分页结构。这是原始查询:

"select `dr`.`sales_price`, `accommodations`.* from `accommodations` left join `accommodation_rooms` as `ar` on `ar`.`id` = `accommodations`.`id` left join `room_pricing_histories` on `room_pricing_histories`.`id` = `ar`.`id` left join `accommodation_rooms` as `br` on `br`.`id` = `accommodations`.`id` inner join `room_capacity_histories` on `room_capacity_histories`.`id` = `br`.`id` inner join `accommodation_rooms` as `cr` on `accommodations`.`id` = `cr`.`accommodation_id` inner join `room_pricing_histories` as `dr` on `cr`.`id` = `room_pricing_histories`.`accommodation_room_id` where `room_pricing_histories`.`sales_price` > ? and `room_pricing_histories`.`sales_price` < ? and date(`room_capacity_histories`.`from_date`) > ? and date(`room_capacity_histories`.`to_date`) < ? group by `dr`.`sales_price`, `accommodations`.`id` order by `dr`.`sales_price` desc"

谢谢

Answer 1

我认为以下代码适合您

排序

$data = $query->join('accommodation_rooms as cr', 'accommodations.id', '=', 'cr.accommodation_id')
             ->join('accommodation_rooms as cr2', 'accommodations.id', '=', 'cr2.accommodation_id')
            ->leftJoin('room_pricing_histories as dr','cr.id', '=', 'room_pricing_histories.accommodation_room_id')
//            ->select('cr.id')

            ->orderBy('dr.sales_price', 'desc')
            ->where('cr.id !=','cr2.id')
            ->select('dr.sales_price', 'accommodations.*')
            ->groupBy('dr.sales_price','accommodations.id');
        $direction = $descending ? 'DESC' : 'ASC';

        return $data;