I have three tables: products, product_inventories and product_inventory_details. The ORM of each model is shown below,

Product Model

class Product extends Model{
    ...

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        ...,
        'title', 
        'selected_inventory_id', 
        ...
    ];

    /**
     * Get the inventories those belongs to this model.
     */
    public function inventory(){
        return $this->hasMany('App\Models\ProductInventory');
    }

    /**
     * Get the selected product_inventory_detail that owns this model.
     */
    public function selected(){
        return $this->hasOne('App\Models\ProductInventoryDetail', 'id', 'selected_inventory_id');
    }
    
    ...
}

ProductInventory Model

class ProductInventory extends Model{
    ...
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'product_id', 
        ...
    ];


    /**
     * Get the inventory_details those belongs to this model.
     */
    public function items(){
        return $this->hasMany('App\Models\ProductInventoryDetail');
    }

    ...
}

ProductInventoryDetail Model

class ProductInventoryDetail extends Model{
    ...
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'product_inventory_id',  
        'price', 
        ...
    ];
}

I'm sorting and limiting the results through user input of Sort by dropdown and Show per page dropdown. When sorting by Alphabetical: High to Low option I'm running the query builder method to order the results:

$products = $products->orderBy($sort['column'], $sort['order'])->paginate($limit);

Now with sorting by Price, I can't run the query builder method orderBy() since I'm not using joins and getting the data through relationship properties. Instead I'm using the Laravel's collection method to sort it out:

$products = $products->paginate($limit);

$products = $products->sortBy(function($prod, $key){
    return $prod->selected->price;
});

The above block is working fine if I don't use pagination methods. But, I need to use the pagination as well since the user can also limit the results per page. I'm also using a Paginator object's method to append some parameters to each page URL:

$products->appends($paramsArr);

Since running the sortBy() method returns a collection instead of Paginator object, it's giving me undefined method exception.

My Question

How can I sort the result set by price in my current scenario without having to implement the joins? Is there a way to achieve that??

CodePudding user response：

I would use QueryBuilder package of Spatie. It will make your life easier for creating sortable and filterable grid table. You use that package this way:

$query = Product::with(['inventory', 'selected']);
$products = \Spatie\QueryBuilder\QueryBuilder::for($query)
    ->allowedFilters([
          'name' => 'name', // name column in the products DB table.
          'selected.price' => 'product_inventory_details.column_price', // price column in the product_inventory_details DB table.
     ])
     ->defaultSort('name')
     ->allowedSorts([
          'name',
          \Spatie\QueryBuilder\AllowedSort::custom('selected.price', new SortSelectedPrice())
     ])
     ->paginate(20)
     ->appends(request()->query());

External custom sort class looks like this:

 class SortSelectedPrice implements \Spatie\QueryBuilder\Sorts\Sort
 {
      public function __invoke(Builder $query, bool $descending, string $property)
      {
           $direction = $descending ? 'DESC' : 'ASC';
           $query->leftJoin('product_inventory_details', 'products.id', '=', 'product_inventory_details.product_id');
           $query->orderBy('product_inventory_details.column_price', direction);
      }
  }

Make sure your URL containing the query string like this for sorting name from A to Z and sorting price from 1xxxxxxxx to 0:

domain.com/products?sort=name,-selected.price

I installed the Spatie package using composer. Don't forget to do that.

CodePudding user response：

I found a way to handle it without having to implement the joins. I added a new variable and stored the Paginator object's items() method result set into it.

$products = $products->paginate($limit);

$productItems = $products->items(); // returns the items array from paginator

And sort that particular variable instead of sorting the whole paginator object. That way my links and URLs are untouched and unmodified in the $products variable and the data of the products are in a separate variable.

if($sort['column'] == 'price'){
    if($sort['order'] == 'DESC'){
        $productItems = $products->sortByDesc(function($prod, $key){
            return $prod->selected->price;
        });
    } else{
        $productItems = $products->sortBy(function($prod, $key){
            return $prod->selected->price;
        });
    }
}

I also had to change my rendering variable from $products to $productItems and accessed the pagination links from the old $products variable.

@forelse ($productItems as $product)
    @include('site.components.product-grid')
@empty
    <div class="col text-center">...</div>
@endforelse

...

{{ $products->links() }}

I'm posting it here for the community to benefit/discuss/criticize if there is a better way.