finding products that customers bought together

I am using Laravel and eloquent If I have the following two tables

Sales
----
|id|
|--|
|1 |
|2 |
|3 |
|4 |
----

Sales_data
----
|sale_id|Product_id|
|------------|
|   1    | 30 |
|   1    | 24 |
|   1    | 18 |
|   2    | 18 |
|   2    | 30 |
|   3    | 24 |
|   4    | 18 |
|   4    | 24 |
--------------

I would like to retrieve the following info:


| Product_id   | bought_with | times_bought_together |
|--------------|-------------|-----------------------|
|       30     |     18      |            2          |
|       30     |     24      |            1          |
|       24     |     18      |            1          |
------------------------------------------------------

This is the relation in Sales model, to the sale_data

    public function data()
    {
        return $this->hasMany(\App\Models\SaleData::class, 'sale_id');
    }

Thank you verry much

chinmaypurav

5 years ago

Level 4

I think you need to rework your database design.

Search for 'laravel eloquent orders design' on youtube and find the video by Andre Madarang. Can't share the link on my first day after sign up.

The second thing, you are using the relationship method the wrong way. And I would recommend you to use Laravel naming conventions.

Proudje

5 years ago

Level 1

So there is no way i can do this?

chinmaypurav

5 years ago

Level 4

Step one: Redesign your database as below

Sales -> Orders One-to-many
Orders <-> Products many-to-many via pivot table order_product

After that think logically about how you can achieve that. Hope this helps.

chinmaypurav

5 years ago

Level 4

Here is one logic, if you want that for a specific product id,

$productId = 1;

$data = Order::whereHas('products', function($query) use (&$productId){
     $query->where('products. id', $productId);
})->get()
->groupBy('order_id');

Do something like this. This is not the final solution. I also digging my way to something similar. But this might help you to figure things out.

1 like

Proudje

5 years ago

Best Answer

Level 1

You can use DB::select() so you can put RAW SQL statements in it. In this case its easyer to just use RAW SQL

DB::select("SELECT c.product_id, c.bought_with, count(*) as times_bought_together
FROM (
  SELECT a.product_id as product_id, b.product_id as bought_with
  FROM sales_data a
  INNER join sales_data b
  ON a.sale_id = b.sale_id AND a.product_id != b.product_id) c
GROUP BY c.product_id, c.bought_with
ORDER BY times_bought_together DESC LIMIT 50")

GoodLuck if you have the same problem!

1 like

Please or to participate in this conversation.