CODEWITHSUNDEEP
phpmysqllaravelleft-join
Hristian Yordanov
Hristian Yordanov

Reputation: 668

Join two tables with all records

I'm tryng to join two tables in my Laravel controller code, and view them in one Datatable.

table1

+--------------------+---------+
|     recordtime     | tempout |
+--------------------+---------+
| 4.12.2020 10:00:00 |     1.1 |
| 4.12.2020 10:30:00 |     1.2 |
| 4.12.2020 11:00:00 |     1.3 |
| 4.12.2020 11:30:00 |     1.4 |
| 4.12.2020 12:00:00 |     1.5 |
+--------------------+---------+

table2

+--------------------+---------+
|     recordtime     | tempout |
+--------------------+---------+
| 4.12.2020 10:00:00 |     2.1 |
| 4.12.2020 11:00:00 |     2.3 |
| 4.12.2020 12:00:00 |     2.5 |
| 4.12.2020 13:00:00 |     2.6 |
| 4.12.2020 14:00:00 |     2.7 |
+--------------------+---------+

When I use this code:

$results  = Tablemodel1::whereBetween('table1.recordtime', $dateScope)
    ->selectRaw('table1.tempout,table2.tempout as tempoutstamb,table2.recordtime')
    ->leftJoin('table2', function($join){
        $join->on('table1.recordtime', '=', 'table2.recordtime');
    })
    ->orderBy('table1.recordtime', 'ASC')
    ->get();

return Datatables::of($results)
    ->make(true);

It's giving me all records that equals to the coresponding recordtime and with aditional records that are on every half hour but with null(invalid date) values from table1. How to display their date instead of null(invalid date)?

+--------------------+---------+--------------+
|     recordtime     | tempout | tempoutstamb |
+--------------------+---------+--------------+
| invalid date       |     1.2 |            - |
| invalid date       |     1.4 |            - |
| 4.12.2020 10:00:00 |     2.1 |          1.1 |
| 4.12.2020 11:00:00 |     2.3 |          1.3 |
| 4.12.2020 12:00:00 |     2.5 |          1.5 |
+--------------------+---------+--------------+

added working Laravel query based on @miken32 answer:

  $results2  = Tablemodel1::whereBetween('table1.recordtime', $dateScope)
    ->selectRaw('table1.recordtime')
    ->selectRaw('max(table1.tempout) as tempout')
    ->selectRaw('max(table2.tempout) as tempoutstamb')        
    ->leftJoin('table2', function($join){
        $join->on('table1.recordtime', '=', 'table2.recordtime');
    })
    ->groupBy('table1.recordtime');
  $results = Tablemodel2::whereBetween('table2.recordtime', $dateScope)
    ->selectRaw('table2.recordtime')
    ->selectRaw('max(table1.tempout) as tempout')
    ->selectRaw('max(table2.tempout) as tempoutstamb')        
    ->leftJoin('table1', function($join){
        $join->on('table1.recordtime', '=', 'table2.recordtime');
    })
    ->groupBy('table2.recordtime')       
    ->orderBy('recordtime', 'ASC')
    ->union($students2)
    ->get();

Upvotes: 0

Views: 62

Answers (1)

miken32
miken32

Reputation: 42713

Here's some SQL that does the trick:

SELECT table1.recordtime, table1.tempout, table2.tempout AS tempoutstamb
FROM table1
LEFT JOIN table2 ON (table1.recordtime = table2.recordtime)

UNION

SELECT table2.recordtime, table1.tempout, table2.tempout AS tempoutstamb
FROM table2
LEFT JOIN table1 ON (table1.recordtime = table2.recordtime)

ORDER BY recordtime

You're looking for a full join, but MySQL doesn't do those. So we fake it with a UNION query.

For use in Laravel, probably easiest to just wrap the whole thing in a raw statement.

Upvotes: 2

Related Questions