Laravel: eloquent orderBy hasOne relation column using with and whereHas

user_17782908 0
Viewed 83

I have a model Product with a hasOne relationship method getCompany().

public function getCompany(){
    return $this->hasOne(Company::class, 'id', 'company_id');
}

I need to retrieve the Product collection sorted by getCompany.rate

My approach


$products = Product::with('getCompanyName', 'getCompany')
    ->whereHas('getCompany', function ($query) {
        return $query->where('status_shop_id', '=', 2);
    })
    ->whereHas('getCompany', function ($query) {
        return $query->where('status', '=', 1);
    })
    ->where('name', 'Like', "%$text%")
    ->whereStatus(1)
    ->whereTypeId(1)
    ->orderBy('rate', 'desc')
    ->limit(5)
    ->get();

Fails with :

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'getCompany.rate' in 'order clause'

I've tried to sort it after collected

return $products->sortBy('getCompany.rate');

But this doesn't sort at all

laravel php
2 Answers

If you want to sort based on the contents of the relationship, you have to do that after the fact. The relations are presented as objects within the main model, and are fetched separately, so you can't expect to do it at the database level.

There's also no need to repeat the whereHas twice; you can just put both conditions in the callback.

$products = Product::with('getCompanyName', 'getCompany')
    ->whereHas('getCompany', function ($query) {
        $query->where('status_shop_id', '=', 2)->where('status', '=', 1);
    })
    ->where('name', 'Like', "%$text%")
    ->whereStatus(1)
    ->whereTypeId(1)
    ->limit(5)
    ->get();
$sorted = $products->sortByDesc('getCompany.rate');

A simple test will show this works effectively:

$coll = collect([
    ["name" => "item 1", "relation" => ["size" => 23]],
    ["name" => "item 2", "relation" => ["size" => 16]],
    ["name" => "item 3", "relation" => ["size" => 15]],
    ["name" => "item 4", "relation" => ["size" => 42]],
]);
dump($coll->sortBy('relation.size'));

Output:

=> Illuminate\Support\Collection {#12167
     all: [
       2 => [
         "name" => "item 3",
         "relation" => [
           "size" => 15,
         ],
       ],
       1 => [
         "name" => "item 2",
         "relation" => [
           "size" => 16,
         ],
       ],
       0 => [
         "name" => "item 1",
         "relation" => [
           "size" => 23,
         ],
       ],
       3 => [
         "name" => "item 4",
         "relation" => [
           "size" => 42,
         ],
       ],
     ],
   }
user_12552890

Best use joins if you want to sort based on another table's column.

@user3532758 Can you show me?

Try something like this:

DB::table('products') //can use Product::join(...) as well and can attach your relationships too, just make sure necessary ids are in the select statement
->join('companies', 'products.id', '=', 'companies.product_id')
->select('products.*', 'companies.name', 'companies.rate')
->orderBy('companies.rate', 'desc')
->get();
user_35327580
Related