如何按自定义追加到模型的关系进行排序

Question

>我有一个自定义的附加关系，我想根据它对我的父母进行排序，所以这是我调用它的 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'),
])

Answer 2

我已经通过添加这个来做到这一点

->with('cheapest_room')
->allowedSorts(['cheapest_room.sales_price'])

Answer 3

我和你面临同样的问题。 我想按关系排序 我发现最好的解决方案是使用join

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'
])
->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.*')
->paginate(10);
return $data;
}

Answer 4

如果住宿类有cheapest_room关系，您应该查看 SortTest.php#L90-103 中的示例代码

public function it_can_sort_a_query_by_a_related_property()
{
$request = new Request([
'sort' => 'related_models.name',
'includes' => 'relatedModel',
]);
$sortedQuery = QueryBuilder::for(TestModel::class, $request)
->allowedIncludes('relatedModels')
->allowedSorts('related_models.name')
->toSql();
$this->assertEquals('select * from "test_models" order by "related_models"."name" asc', $sortedQuery);
}

尝试

$query->allowedSorts('cheapest_room.sales_price')

如果对cheapest_room属性有复杂的查询，则应定义自定义排序，有关详细信息，请参阅 https://github.com/spatie/laravel-query-builder/pull/91#issue-208809827。

class CheapestPriceSort implements SpatieQueryBuilderSortsSort
{
public function __invoke(Builder $query, bool $descending, string $property)
{
$query->... // Run {join,sub} query with $query
return $query->orderBy('cheapest_room.sales_price', $descending ? 'desc' : 'asc');
}
}