php - 如何按自定义附加关系对模型进行排序

Question

我有一个与模型的自定义附加关系，我想根据它对我的 parent 进行排序，所以这是我调用它时 api 的结果:

  {
            "id": 3,
            "operator_id": 12,
            "created_at": "2019-05-22 19:20:02",
            "updated_at": "2019-07-31 18:50:53",
            "hits": 4,
            "cheapest_room": {
                "id": 1,              
                "accommodation_id": 3,
                "bed_count": 1,  
                "name": null,
                "english_name": null,
                "description": null,
                "english_description": null,
                "created_at": "2019-05-25 13:30:00",
                "updated_at": "2019-06-30 17:05:18",
                "accommodation_room_id": 1,
                 // this is the field i want to sort by
                "sales_price": 100,
                "from_date": "2019-05-25 00:00:00",
                "to_date": "2019-08-30 23:59:59"
            }

我正在使用 spatie 查询生成器对此进行排序和过滤，但我想对最便宜的房间对象的 sales_price 进行排序这是我的代码如下:

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

        return $data;
    }

简而言之，我想根据销售价格对我的 api 进行排序

Answer 1

您应该使用如下所示的连接，它将根据您的需要为您提供所有东西

  $data = $query->join('accommodation_rooms', 'accommodations.id', '=', 'accommodation_rooms.accommodation_id')
            ->join('discounts', 'accommodation_rooms.id', '=', 'discounts.accommodation_room_id')
            ->select('accommodation_rooms.id')
            ->orderBy('discounts.amount', 'desc')
            ->select('discounts.amount', 'accommodations.*')
            ->groupBy('discounts.amount', 'accommodation_rooms.id');
        return $data;

之后你可以像下面这样在你的过滤器中使用它:

    ->allowedSorts([
                AllowedSort::custom('discount', new DiscountSort() ,'amount'),
            ])