Filter most sold product - Laravel 9

Hi, i need to filter the most sold products and show later in a view, (top 10 most sold)

i have 3 tables:

products (general product info) -> id | name
invoice_items -> name | item_quantity | product_id
saleorder_items -> name | item_quantity | product_id

as you may guess in products table i have my list of products and when a purchase is made it goes into invoice_items table or saleorder_items table according to customers invoice request.

if a product id is in both tables combine them and count up

so far i have done this:

$products = DB::table('products') ->leftJoin('invoice_items', 'products.id', '=', 'invoice_items.product_id') ->selectRaw('products.*, COALESCE(sum(invoice_items.item_quantity),0) sold') ->groupBy('products.id') ->orderBy('sold','desc') ->take(10) ->get();

above code works and i get a list of top 10 invoice_items product, however when i want to join saleorder_items table because there might be product with same id so i can count that as a total, it goes wrong and shows only the products which are in both table only...

$products = DB::table('products') ->leftJoin('invoice_items', 'products.id', '=', 'invoice_items.product_id') ->leftJoin('saleorder_items', 'products.id', '=', 'saleorder_items.product_id') ->selectRaw('products.*, COALESCE(sum(invoice_items.item_quantity + saleorder_items.item_quantity),0) sold') ->groupBy('products.id') ->orderBy('sold','desc') ->take(10) ->get();

tisuchi

3 years ago

Level 70

@askinsavascisi How about this?

$products = DB::table('products')
    ->leftJoin(DB::raw("(SELECT product_id, sum(item_quantity) as sold FROM invoice_items GROUP BY product_id) as invoice_items"), function($join) {
        $join->on('products.id', '=', 'invoice_items.product_id');
    })
    ->leftJoin(DB::raw("(SELECT product_id, sum(item_quantity) as sold FROM saleorder_items GROUP BY product_id) as saleorder_items"), function($join) {
        $join->on('products.id', '=', 'saleorder_items.product_id');
    })
    ->selectRaw('products.*, COALESCE(sum(invoice_items.sold + saleorder_items.sold), 0) as sold')
    ->groupBy('products.id')
    ->orderBy('sold', 'desc')
    ->take(10)
    ->get();

AskinSavascisi

3 years ago

Level 1

@tisuchi first of all i'd like to thank you for your response. we're close but not there yet :)

atm i've filled in some dummy data in "saleorder_items" and "invoice_items", saleorder_items has 1 row and invoice_items 3..

the product in saleorder_items , i do have it also in invoice_items, your code did find both and sum them correctly however it didnt filter other products in invoice_items.. see screenshot below please.

ss table saleorder_items: https : // prnt . sc / 8yo1fueu_pYe

ss table invoice_items: https : // prnt . sc / snA4SSDLTcB-

ss output of code: https : // prnt . sc/oMhe9Yx9EMw2

Kind regards,

Atabey

Please or to participate in this conversation.