时间戳

首页 > 解决方案 > 想要在 Laravel 中获取相关表中具有共同行的最大记录

laravel - 想要在 Laravel 中获取相关表中具有共同行的最大记录

问题描述

我在 laravel 中有三张桌子，一张是中间桌子：

CREATE TABLE 订单（id INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT, order_number VARCHAR(100) NOT NULL）；

CREATE TABLE 产品（id INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT，title VARCHAR(100) NOT NULL）；

创建表 orders_products ( order_id INT(10) NOT NULL, product_id INT(10) NOT NULL );

我想检索有共同产品的前两个订单，数字越大，这里无关紧要，

重要的是 orderC 有 5 个订单，1,3,4,5,7 和 orderF 也有 1,2,3,4,5,6,8 所以他们有 1,3,4,5 的共同点，这就是我想要的，即使别人有一个2,7,9,12,13,15,17,23和另一个2,4,6,8,9,11,12,14,15,16， 17 这不是我想要的，但那些有最多共同点的产品

非常感谢

这给出了不准确的结果：

  $orders = 
  DB::table('orders')
 ->select('orders.id as ordid', 'orders.order_number as order_number',

     DB::raw('COUNT(orders_products.product_id) as counter'  )  )
    ->join('orders_products', 'orders.id', '=', 'orders_products.order_id')
    ->join('products as prod1', 'prod1.id', '=', 

     'orders_products.product_id')
    ->join('products as prod2', 'prod1.id', '=', 'prod2.id')
    ->groupBy(  'orders.order_number')
    ->orderByRaw('MAX(orders_products.product_id) DESC')

    ->limit(2)
    ->get();

标签： laraveleloquent

解决方案

To get the orders with the highest amount of products, you need to use HAVING clause and a subquery to get desired results.

The query below works like this:

  1. First, a subquery (with a nested subquery) finds the highest amount of products in a single order - the highest_amount value
  2. Then a wrapping query finds all orders, that have as many products in order as the highest amount from a subquery.

It would look something like this:

SELECT 
   orders.id as ordid, 
   orders.order_number as order_number, 
   COUNT(orders_products.product_id) as counter
FROM orders
JOIN orders_products ON orders.id = orders_products.order_id
JOIN products ON products.id = orders_products.product_id
GROUP BY orders.order_number
HAVING COUNT(orders_products.product_id) >= (
   SELECT MAX(products_in_order) as highest_amount FROM (
      SELECT COUNT(orders_products.product_id) as products_in_order
      FROM orders
      JOIN orders_products ON orders.id = orders_products.order_id
      JOIN products ON products.id = orders_products.product_id
      GROUP BY orders.order_number
   ) top_amounts
)

I tested it in this SQL fiddle - it works as expected (you get all orders with the most products - 3 in this case)

推荐阅读