Remove duplicated data based on quintity

  database, eloquent, laravel

Want to remove duplicate data based on Quantity,

DB::table(DB::raw('tenant_db.sales_data as b'))
            ->selectRaw('a.product_id as product_id, b.product_id as bought_with, count(*) as times_bought_together, SUM(a.qty) AS aQuantity, SUM(b.qty) AS bQuantity')
            ->join(DB::raw('tenant_db.sales_data as a'), function ($join){
                $join->on('a.sale_id', '=', 'b.sale_id');
                $join->on('a.product_id', '<>', 'b.product_id');
            ->groupBy('a.product_id', 'b.product_id')
            ->orderBy('times_bought_together', 'DESC')

Something like this

            ->having('aQuantity', '>', 'bQuantity')

Result that I get

    0 => {#741 ▼
      +"product_id": 41
      +"bought_with": 42
      +"times_bought_together": 3
      +"aQuantity": "4"
      +"bQuantity": "3"
    1 => {#743 ▼
      +"product_id": 42
      +"bought_with": 41
      +"times_bought_together": 3
      +"aQuantity": "3"
      +"bQuantity": "4"

I only want to see the results when aQuantity is higher as bQuantity, but I need it in my query because otherwise it wont work with my Datatable from yarja

Source: Laravel

Leave a Reply