Want to get maximum records which have common rows in related table

Hello,

I have three tables in laravel, one is a middle table :

CREATE TABLE orders( id INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT, order_number VARCHAR(100) NOT NULL );

CREATE TABLE products( id INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT, title VARCHAR(100) NOT NULL );

CREATE TABLE orders_products ( order_id INT(10) NOT NULL, product_id INT(10) NOT NULL );

I want to retrive the top two orders which have common products, the higher number is irrelevant here,

What counts is that orderC which has 5 orders, 1,3,4,5,7 and orderF which have also 1,2,3,4,5,6,8 so they have 1,3,4,5 in common, this is what I want to have, even if others have one 2,7,9,12,13,15,17,23 and other 2,4,6,8,9,11,12,14,15,16,17 that is not what I want, but the ones have the most products in common

Many Thanks

petermanoukian

7 years ago

Level 1

This is giving inaccurate results:

    $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();

Please or to participate in this conversation.