Eloquent: how to join a directly linked table and an indirectly linked table?

Question

To demonstrate my problem, I created a dummy database with these 4 tables (defined as Eloquent models): User, Product, ShoppingItem (short: Item), Order; with all their relevant relationships. (User is included for completeness only)
Products:

• ID
• name
• hasMany (item)

Orders:

• ID
• user_id
• the date
• hasMany (item)

Products:

• ID
• product_id
• order_id
• belongsTo (Product)
• belongsTo (Order)

Thus, (Shopping) Items records are created whenever a customer purchases certain products. An order (trading card) is also created and contains 1 or more items belonging to exactly one client (User) and his current shopping process.

Now this problem is connected with the list of products and the way of analyzing their "success". How often are they sold and when was the last time they were sold?

I can, for example, use the following query to get all Orders for a single product and, therefore, calculate 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(); 

In addition, I can get a list of all the products ordered by the amount of time they were 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 (bought). This result should be an Eloquent query query or a set of Query Builder, so that I can use pagination.

I have a mutator defined in my product model, for example:

 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 last purchase date of this product, and I can use it in the view as follows:

 {{ $product->last_time_sold }} 

However, I cannot use 'last_time_sold' in an Eloquent OrderBy statement!

Is there any other way to get the "last_time_sold" value of a product in a database relationship as described above without losing the ability to paginate?

Ultimately, what is the correct way to request a product table and order products the last time they were ordered?

Remember that the elements do not have a date, only the order (Trade Card) has a date.

Answer 1

Use LEFT JOINs with tables items and orders , group by products and order MAX(orders.date) .

 $products = Product::leftJoin('items', 'items.product_id', '=', 'products.id') ->leftJoin('orders', 'orders.id', '=', 'items.order_id') ->groupBy('products.id') ->selectRaw('products.*, max(orders.date) as last_ordered') ->orderBy('last_ordered', 'desc') ->get(); 

This will execute the following query:

 select products.*, max(orders.date) as last_ordered from `products` left join `items` on `items`.`product_id` = `products`.`id` left join `orders` on `orders`.`id` = `items`.`order_id` group by `products`.`id` order by `last_ordered` desc 

Note 1: If you want to receive the items you just ordered, use join() instead of leftJoin .

Note 2: If you start MySQL with strict mode (the default for laravel 5.3), you will need to include all the columns from the products table in the groupBy() .

Note 3: For products that have never been ordered, the last_ordered column will be NULL. In MySQL, NULL is first ordered using ASC and used when using DESC . But AFAIK is not documented. Therefore, if you do not want to rely on this behavior, use:

 ->orderbyRaw('max(orders.date) is null') ->orderBy('last_ordered', 'desc') 

Answer 2

Depending on how often you run this query, it might be better to create a "last_ordered" column for the timestamp for the products and touch it every time there is an order.