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(...))?
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.