Eloquent: How to join a directly related table and a indirectly related table?

Question

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.

Answer 1

Use LEFT JOINs with the items and orders tables, group by products and order by 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 only want to get products that has already been ordered - use join() instead of leftJoin.

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

Note 3: For products that have never been ordered the last_ordered column will be NULL. In MySQL NULL is ordered first when using ASC and last when using DESC. But AFAIK this is not documented. So if you don't want to rely on that behaviour, use:

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