how to sort by a custom appended relation to model

user_3357687 0
Viewed 2499

i have a custom appended relation to model that i want to sort my parents according to that so this is the result of my api when i call it :

  {
            "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"
            }

i am using spatie query builder for sorting and filtering this but i want to do a sort on sales_price on cheapest room object this is my code below :

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

in short form i want to sort my api based on sales price

laravel php
4 Answers

i have done this by adding this

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

You should Use A join like below which will give you All things according to your needs

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

after that you can user it in your filters like below : 

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

I face the same issue as you. I wanted to sort by the relationship The best solution I found is to use 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;
}
user_18685920

If Accommodation class have cheapest_room relation, You should check out the sample code from 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);
}

Try

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

If you have complex query for cheapest_room property, You should define custom sort, See https://github.com/spatie/laravel-query-builder/pull/91#issue-208809827 for detail.

class CheapestPriceSort implements \Spatie\QueryBuilder\Sorts\Sort
{
    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');
    }
}
user_1576750
Related