Laravel：将2个查询合并为1个 [英] Laravel: combine 2 queries into 1

问题描述

我有以下关系：

订单-> manyToMany-> 产品

'orders' -> manyToMany -> 'products'

和

订单-> manyToMany-> ‘collis’-> manyToMany产品

'orders' -> manyToMany -> 'collis' -> manyToMany products

不要管图像中的其他表格。

Don't mind the other tables in the image.

我想检索所有订购的产品，并获取每种产品的总量。结果应类似于：

I want to retrieve all products that are ordered and get the total quantity of each product. The result should look something like:

[{id: 6, name: "steak", category_name: "beef", total_product_quantity: "2.00"}
{id: 7, name: "bacon", category_name: "pork", total_product_quantity: "1.00"}
{id: 9, name: "chicken filet", category_name: "chicken", total_product_quantity: "1.00"}

colli的帐户

$allProducts = DB::query()
      ->select(['p.id', 'p.name', 'c.name as category_name', DB::raw('sum(op.quantity) as total_product_quantity')])
      ->from('products as p')
      ->join('order_product as op', 'p.id', '=', 'op.product_id')
      ->join('orders as o', 'op.order_id', '=', 'o.id')
      ->join('categories as c', 'p.category_id', '=', 'c.id')
      ->groupBy('p.id');

以及colli的所有产品，以及

and all products that were present in colli's with

$allProductsInAllCollis = DB::query()
      ->select(['p.id', 'p.name', 'c.name as category_name', DB::raw('sum(co.quantity * colp.quantity) as total_product_quantity')])
      ->from('products as p')
      ->join('colli_product as colp', 'p.id', '=', 'colp.product_id')
      ->join('collis as col', 'colp.colli_id', '=', 'col.id')
      ->join('colli_order as co', 'col.id', '=', 'co.colli_id')
      ->join('orders as o', 'co.order_id', '=', 'o.id')
      ->join('categories as c', 'col.category_id', '=', 'c.id')
      ->groupBy('p.id', 'p.name', 'category_name');

两个查询都返回与上述相同的表结构（数组）。但是现在我想将第一个表的total_product_quantity添加到第二个表，并返回合并的表。我该怎么做？

Both queries return the same table structure (array) as mentioned above. But now I want to add the total_product_quantity from the first table to the second one, and return the merged table. How can I do this?

我对sql甚至laravel查询生成器的了解都很少，所以如果有更好的方法来编写查询（也许更雄辩？），请允许我

My knowledge of sql and even the laravel query builder is quite low, so if there are better ways to write the queries (more eloquent maybe?), let me know please!

推荐答案

您可以使用关系来获取数据，
这种关系是hasManyThrough

you can use relationships to get data, this relationships is hasManyThrough

尝试一下：

在模型中订购

public function products(){
        return $this->hasManyThrough(Colli::class, Product::class)->withPivot('quantity');

    }

这篇关于Laravel：将2个查询合并为1个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！