php - 如何从laravel的2个联接中删除重复项

我正在使用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;
    }

secound过滤器也作为范围

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"

谢谢

最佳答案

我认为以下代码将为您工作

分类

$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;

关于php - 如何从laravel的2个联接中删除重复项，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58628667/