CODEWITHSUNDEEP
laraveleloquent
joenpc npcsolution
joenpc npcsolution

Reputation: 767

How to get product list related to other sale transaction count in Laravel?

I'm a newbie in Laravel and working on a small e-commerce project. I need to get products list that related to transaction count in the transaction table. Below is my demo data.

tb_product:

product_id    product_name
    1             A
    2             B  
    3             C

tb_transaction:

transaction_id    product_id
    1                2
    2                1
    3                2
    4                2
    5                1
    6                3

From tb_transaction. I have sale list as below

B = 3sales, A =2sales, C=1sale

I need to generate Laravel eloquent result like [{B...},{A...},{C...}] (count from max to min). I had tried some from google but no luck. Appreciated for advice, Thanks.

Upvotes: 0

Views: 502

Answers (1)

Sandeep Sudhakaran
Sandeep Sudhakaran

Reputation: 1092

Try the below query,

DB::table('tb_product')
->select('tb_product.product_id', 'tb_product.product_name', DB::raw("count('tb_transaction.product_id') as salesCount"))
->join('tb_transaction', 'tb_transaction.product_id', 'tb_product.product_id')
->orderBy('salesCount', 'DESC')
->groupBy('tb_product.product_id', 'tb_product.product_name')
->get()

Upvotes: 1

Related Questions