how to sort by a custom appended relation to model

phplaravel


PHP Snippet 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;

PHP Snippet 2:

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

PHP Snippet 3:

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

PHP Snippet 4:

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

PHP Snippet 5:

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

PHP Snippet 6:

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

PHP Snippet 7:

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

Related Snippets