To demonstrate my problem, I have created a fictitious database with these 4 tables (defined as Eloquent models): User, Product, ShoppingItem (short: Item), Order; with all their relevant relations. (User included here only for completeness)
Products:
- id
- name
- hasMany(Item)
Orders:
- id
- user_id
- date
- hasMany(Item)
Items:
- id
- product_id
- order_id
- belongsTo(Product)
- belongsTo(Order)
So the (Shopping) Items records are created whenever a customer buys some products. An Order (the shopping kart) is created as well and contains 1 or more Items belonging to exactly one customer (User) and his current shopping process.
Now this problem is about the list of products and a way to analyze their 'success'. How often are they sold and when was the last time one was sold?
I can, for instance, use the following query to get all Orders for a single product and therefore to count how often they are sold:
$orders = Order::whereHas('items', function ($query) use ($id) {
$query->where('product_id', $id)
->where('date', '<', Carbon::now());
})->orderBy('date', 'desc')->get();
Also, I can get a list of all products ordered by the amount of time they have been sold with this:
$products = Products::withCount('items')
->orderBy('item_count', 'desc');
But I want to get a list of all products, sorted by the date when they were last ordered (purchased). That result must be an Eloquent query or a Query Builder set, so that I can use pagination on it.
I have a Mutator defined in my Product model, like this:
public function getLastTimeSoldAttribute( $value ) {
$id = $this->id;
// get list of plans using this song
$order = Order::whereHas('items', function ($query) use ($id) {
$query->where('product_id', $id)
->where('date', '<', Carbon::now());
})->orderBy('date', 'desc')->first();
return $order->date;
}
It returns the date of the last purchase of this product and I can use it in the view like this:
{{ $product->last_time_sold }}
However, I can't use 'last_time_sold' in an Eloquent OrderBy statement!
Is there another way to get the value of 'last_time_sold' of a product within the database relationships as described above without losing the ability to do Pagination?
Ultimately, what is the proper way to query the Products table and order the products by the last time they have been ordered?
Mind you, the items don't have a date, only the order (the Shopping Kart) has a date.
$query ->where('date', '<', Carbon::now());
supposed to work? – Sanzeeb Aryal$items = Item::with('product','order')->orderBy('product_id','date')
but that produces a list of all items, not of all products (which is much smaller). – matthiku