雄辩:如何将直接相关的表和间接相关的表连接起来？

Question

为了演示我的问题，我创建了一个虚拟数据库，它包含以下四个表(定义为雄辩的模型)：用户、产品、ShoppingItem (短: Item)、Order；以及它们的所有相关关系。(此处仅包括用户的完整性)

产品：

• id
• 名字
• hasMany(项目)

订单：

• id
• user_id
• 日期
• hasMany(项目)

项目：

• id
• product_id
• order_id
• belongsTo(产品)
• belongsTo(订单)

因此，每当客户购买某些产品时，就会创建(购物)项目记录。订单(购物卡)也被创建，并且包含一个或多个属于一个客户(用户)和他当前的购物过程的项目。

现在这个问题是关于产品清单和分析他们的“成功”的方法。他们多久卖一次，上一次被卖是什么时候？

例如，我可以使用以下查询来获取单个产品的所有订单，从而计算出它们被出售的频率：

$orders = Order::whereHas('items', function ($query) use ($id) {
    $query->where('product_id', $id) 
          ->where('date', '<', Carbon::now());
})->orderBy('date', 'desc')->get();

此外，我还可以得到所有产品的清单，这些产品的销售时间如下：

$products = Products::withCount('items')
                    ->orderBy('item_count', 'desc');

--但我想得到所有产品的列表，按它们上次订购(购买)的日期排序。必须是一个有说服力的查询或查询生成器集，这样我就可以在上面使用分页了。

我在产品模型中定义了一个Mutator，如下所示：

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;
}

它返回最后一次购买此产品的日期，我可以在视图中使用它，如下所示：

{{ $product->last_time_sold }}

然而，我不能用'last_time_sold‘在一个雄辩的OrderBy声明！

是否有另一种方法可以在数据库关系中获得产品的“last_time_sold”值，而不丧失分页功能？

最终，查询产品表并在上一次订购产品时订购产品的正确方法是什么？

请注意，这些物品没有日期，只有订单(购物卡)有日期。

Answer 1

在items和orders表中使用左联接，按products分组并按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();

这将执行以下查询：

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

备注1:如果您只想获得已经订购的产品，请使用join()而不是leftJoin。

备注2:如果您在严格模式下运行MySQL (Laravel5.3的默认值)，则需要在groupBy()子句中包括products表中的所有列。

注释3:对于从未订购过的产品，last_ordered列将为NULL。在MySQL中，NULL首先在使用ASC时排序，最后在使用DESC时排序。但AFAIK并没有记录在案。因此，如果您不想依赖这种行为，请使用：

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

Answer 2

根据执行此查询的频率，最好为产品上的时间戳创建一个"last_ordered“列，并在每次有订单时触摸它。