Laravel: order by relationship (product sorted by first order date)

user_678951 0
Viewed 310

I have products and orders. For a product I can get the orders and also get the first order (using recently added ofMany):

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

public function firstOrder() {
    return $this->hasOne(Order::class)
        ->ofMany('created_at', 'min');
}

I can get products with recent first orders, but I'm not sure how to order the products by that date:

Product::whereHas('firstOrder', fn($q) => $q->where('created_at', '>=', now()->subDays(7)))
    ->with('firstOrder')
    // ->orderBy('firstOrder.created_at') 
    // orderBy fails: The multi-part identifier "firstOrder.created_at" could not be bound.

How can I get the products ordered by first order date that have a recent (e.g. last week) first order as part of the database query (so: no ->get()->sortBy(...))?

eloquent laravel php
1 Answers

What you're trying to do is not easy out of the box.

One way is to use a subquery join (you can move the date condition to the subquery)

$firstOrders = DB::table('orders')
    ->select('product_id', DB::raw('MIN(created_at) as first_order_created_at'))
    ->where('created_at', '>=', now()->subDays(7))
    ->groupBy('product_id');

$products = Product::with('firstOrder')
    ->joinSub($firstOrders, 'first_orders', function ($join) {
            $join->on('products.id', '=', 'first_orders.product_id');
        })
    ->orderBy('first_orders.first_order_created_at', 'ASC')
    ->get();

Hope it gives you at least a lead to what you're trying to do.

user_43699190
Related