CODEWITHSUNDEEP
laravellaravel-5eloquentrelationship
Muhammad Abbas
Muhammad Abbas

Reputation: 129

How to order by eager loaded relationship in Laravel 5.5?

The code below works perfectly and displays all products with discounts in JSON Format for my API. But I want the result ordered by id in the discounts table. Something like orderBy('discount.id', 'desc'). Can anyone provide a solution for this? How it is possible to use orderBy with id column in discount table using has()?

public function promotions()
{
    return $this->prepareResult(true, Product::has('discount')->where([
        'company_id' => 1, 'is_active' => 1
    ])->with('category')->with('discount')->get(), [], "All Promotions");
}

Upvotes: 5

Views: 4345

Answers (3)

Narc0t1CYM
Narc0t1CYM

Reputation: 632

As DevK mentioned, you need to do a join and then you can sort your products by that. The trick here is that you join the discounts table to your products table, but only select the id column (named as discount_id) from your discounts table so you can sort by those records.

In the below example, I assumed that your

  • Category model's table is categories
  • Product model's table is products
  • Discount model's table is discounts
  • products table references a discount.id as discount_id

This code will return every column from the products table plus a discount_id column, which you can ignore, it's only there for the sorting. It will also keep it as a collection with the relationships that you stated above.

public function promotions()
{
    return $this->prepareResult(
        true,
        Product::has('discount')
            ->where(['company_id' => 1, 'is_active' => 1])
            ->with('category')
            ->with('discount')
            ->selectRaw('products.*, discounts.id as discount_id')
            ->join('discount', 'products.discount_id', '=', 'discounts.id')
            ->orderBy('discount_id', 'DESC')
            ->get(),
        [],
        "All Promotions"
    );
}

Upvotes: 0

Alex Harris
Alex Harris

Reputation: 6412

You can use a function within your with statement:

public function promotions()
{
    return $this->prepareResult(true, Product::has('discount')
        ->where(['company_id'=> 1, 'is_active'=>1])
        ->with('category')
        ->with(['discount' => function ($query) {
            return $query->orderBy('id','DESC');
        }])->get(), [],"All Promotions");
}

You can read about this here in the documentation.

Upvotes: 2

N Mahurin
N Mahurin

Reputation: 1446

If you want to go the collection method route instead of Alex's answer (which is also valid), you can just continue to chain collection methods after get. Since you included the with(), you can do

->get()->sortBy('discount.id')

https://laravel.com/docs/5.6/collections#method-sortby

Not related to your question, but wanted to point out that you can pass multiple arguments to with() so that you don't call it twice.

Product::has('discount')->where(['company_id'=> 1, 'is_active'=>1])->with('discount', 'category')->get()

Upvotes: 0

Related Questions