how to write orderBy query for a attribute with some condition like has relation or not, in Laravel-CodePudding

I want write a query for get all products list with their types. and I like to set order to price. My models are like this:

Product:
    id
    price
    ...

ProductType:
    id
    product_id
    price
    ...

Products have price attribute and some products have types (one to many relationship). each type of products have own price. when a product have type, it's price is 0. it means product type's price is important for us. now when I want to write orderBy to price I don't know how to write query that contains both price I mean when product has type, we query for first type price and when we don't have type, we must query to products own price. I do all of these in Laravel.

Products::with('types')->orderBy(???)->paginate(15);

CodePudding user response：

Using sortBy... could help.

Products::with('productType')->get()->sortByDesc('productType.price');

or use

[edit after comment]

$sortDirection = 'desc';

  Products::with(['productType' => function ($query) use ($sortDirection) {
        $query->orderBy('price', $sortDirection);
    }])->get()->sortByDesc('product.price');

or model

It is possible to extend the relation with query functions:

 public function productType()
    {
        return $this->hasMany('ProductType')->orderBy('price');
    }

CodePudding user response：


Products::with('productType')
    ->orderBy('price')
    ->orderBy(
          ProductType::select('price')
             ->whereColumn('products.id', '=' , 'product_types.product_id')
     );

You can use as a scope and you can pass the order Direction to it so you can use desc and asc, for example

Product.php


public function scopeOrderedByProductTypesPrice($query, $order="asc"){

  $query
    ->orderBy('price')
    ->orderBy(
          ProductType::select('price')
             ->whereColumn('products.id', '=' , 'product_types.product_id')
     );
}

use case:


Products::with('productType')
        ->OrderedByProductTypesPrice('desc')
        ->get()

PS: as you may see you can also update the scope and accept the column as an argument the the sorting