How to join three tables with where condition to get sum of two relational table’s amount for 1M data faster query

  join, laravel, laravel-query-builder, mysql, sum

I have three tables: super_consumers , balance_ins, recharges

super_consumers table:

id            name
----------    ----
1              A
2              B

balance_ins table:

id    super_consumer_id  amount   type(1,2)
---   -----------------  ------   ----
1            1             10       1
2            1             20       1
3            2             10       2

recharges table:

id    super_consumer_id  amount  status(0,1)
---   -----------------  ------  ------
1            1             5       1
2            1             10      1
3            2             15      0

I want to get each super_consumers table’s Total Balance In amount and Total recharge amount by following some condition below,

  1. Total_balance_in amount from balance_in table where type == 1
  2. Total_recharge amount from recharges table where status == 1

Expected result :

[
    'id' = 1,
    'name' = 'A',
    'total_balance_in' = 30,
    'total_recharge' = 15
],
[
    'id' = 2,
    'name' = 'B',
    'total_balance_in' = 0,
    'total_recharge' = 0
]

How can I properly write this Query in ‘laravel DB Query’ or ‘mysql’ ?

Source: Laravel

Leave a Reply