3

I have two models with relations as defined below

Order

public function owner()
{
    return $this->belongsTo(User::class, 'owner_id');
}

User

public function company(){
    return $this->belongsTo(Company::class, 'company_id');
}

company table have 'title' field. what I want is to get all the orders sorted/order by company title. I've tried different solution but nothing seems to work. Any help or hint would be appreciated.

Recent solution that I tried is

$query = OrderModel::whereHas('owner', function($q) use ($request){
             // $q->orderBy('owner');
             $q->whereHas('company',function ($q2) use ($request){
                 $q2->orderBy('title',$request->get('orderByDirection') ?? 'asc');
             });
         });

but I am not getting user and company relation in query results. also the result remains same for 'ASC' and 'DESC' order.

Afzal Ali
  • 880
  • 8
  • 25

2 Answers2

2

You could sort the query after adding join like:

return Order::join('users', 'users.id', '=', 'owner_id')
        ->join('companies', 'companies.id', '=', 'users.company_id')
        ->orderBy('companies.title')
        ->select('orders.*')
        ->get();
Rateb Habbab
  • 1,739
  • 8
  • 13
  • Thanks alot. For my case it is better with left join because i want all orders even if there is no company associated with it. – Afzal Ali Sep 27 '21 at 08:28
0

You can define new relations in User and Company models.

User

public function orders()
{
    return $this->hasMany(Order::class);
}

Company

public function users()
{
    return $this->hasMany(User::class);
}

Now you can fetch companies that are in asc order and with the use of relation, you can fetch users and orders. So the ORM like be,

$companies = Company::with('users.orders')->orderBy('title', 'ASC')->get();

So these are the company-wise orders. You can use this too.

Vivek Pawar
  • 664
  • 5
  • 11
  • Appreciated your effort but this is not what I need. I want orders order by company but the above query returns companies – Afzal Ali Sep 27 '21 at 08:30
  • Yes, the above query returns companies along with user's orders. I am just used ORM with the defined relations and I think it's the appropriate way to do it. Otherwise, we all can use query builder. not a big deal. Thanks, Happy Coding :) – Vivek Pawar Sep 27 '21 at 09:42