Laravel数据库加入sql或Array连接

Question

I have two tables:

1. outgo_table
   • 1.1 daily_money_column
   • 1.2 created_at_column
2. income_table**
   • 2.1 daily_money_column
   • 2.2 created_at_column

What I want to select?

1. I want to select sum of daily_money_column using created_at_column from two table
2. I want to join them with daily created_column

for example: outgo_table

---

|daily_money_column| created_at_column    |
|    100           |  2018-07-15 13:21:00 |
|    150           |  2018-07-14 13:44:00 |
|    200           |  2018-07-14 13:11:00 |
|    170           |  2018-07-12 13:14:00 |
|    500           |  2018-07-11 13:48:00 | 

income_table

|daily_money_column| created_at_column    |
|    200           |  2018-07-15 13:42:00 |
|    300           |  2018-07-15 13:42:00 |
|    200           |  2018-07-13 13:42:00 |
|    200           |  2018-07-11 13:42:00 |
|    200           |  2018-07-11 13:42:00 |
|    50000         |  2018-07-10 13:42:00 | 

I want such a result ?

| daily_money_column_from_outgo | daily_money_column_from_income | created_at |
|    100                        |             500*               | 2018-07-15 |
|    350                        |              0                 | 2018-07-14 |
|    0                          |             200                | 2018-07-13 |
|    170                        |              0                 | 2018-07-12 |
|    500                        |             400                | 2018-07-11 |
|    0                          |            50000               | 2018-07-10 |

Answer 1

You can do it by:

DB::table('outgo_table')->select(array(
    DB::raw('sum(outgo_table.daily_money_column)'),
    DB::raw('sum(income_table.daily_money_column)'),
    DB::raw('DATE_FORMAT(income_table.created_at, "%Y-%m-%d")')
))->join('income_table', function($query){
    $query->on(DB::raw('DATE_FORMAT(outgo_table.created_at, "%Y-%m-%d")'), '=', DB::raw('DATE_FORMAT(income_table.created_at, "%Y-%m-%d")'))
})->groupBy(DB::raw('DATE_FORMAT(income_table.created_at, "%Y-%m-%d")'))
  ->get();