title: Laravel, MYSQL full join workaround on two aggregated tables
body: <p>I've got a table called <code>transactions</code> with below schema:</p>
  <pre><code>TRANSACTIONS
  +----+-----------------+-------------------+--------+
  | id | to (account_id) | from (account_id) | amount |
  +----+-----------------+-------------------+--------+
  | 1  | 1               | 3                 | 500    |
  +----+-----------------+-------------------+--------+
  | 2  | 2               | 1                 | 250    |
  +----+-----------------+-------------------+--------+
  | 3  | 1               | 2                 | 100    |
  +----+-----------------+-------------------+--------+
  | 4  | 4               | 2                 | 50     |
  +----+-----------------+-------------------+--------+
  </code></pre>
  <p><code>to</code> and <code>from</code> are foreign keys to <code>id</code> on table <code>accounts</code>.</p>
  <p>I wanna get the <code>subtraction</code> of <code>sum</code> of <code>all received transactions</code> from <code>all paid transactions</code> of <code>selected accounts</code> as in the example accounts <code>1</code> and <code>2</code> and <code>all the accounts which whom those had transaction with</code>.</p>
  <p>This is the desired result:</p>
  <pre><code>DESIRED QUERY RESULT
  +---+------------+---------------+-----------+---------+
  | # | account_id | totalReceived | totalPaid | balance |
  +---+------------+---------------+-----------+---------+
  | 1 | 1          | 600           | 250       | 350     |
  +---+------------+---------------+-----------+---------+
  | 2 | 2          | 250           | 150       | 100     |
  +---+------------+---------------+-----------+---------+
  | 3 | 3          | 0             | 500       | -500    |
  +---+------------+---------------+-----------+---------+
  | 4 | 4          | 50            | 0         | 50      |
  +---+------------+---------------+-----------+---------+
  </code></pre>
  <p>What I've got so far:</p>
  <p>as I'm using Laravel with query builder I run it like follows:</p>
  <pre><code>$k = [1, 2];
  
  $receivedTransactions = DB::table('transactions as t1')-&gt;selectRaw('t1.to as account_id, sum(t1.amount) as totalReceived')
      -&gt;whereIn('t1.from', $k)-&gt;orWhereIn('t1.to', $k)-&gt;groupBy('t1.to');
  
  $paidTransactions = DB::table('transactions as t2')-&gt;selectRaw('t2.from as account_id, sum(t2.amount) as totalPaid')
      -&gt;whereIn('t2.from', $k)-&gt;orWhereIn('t2.to', $k)-&gt;groupBy('t2.from');
  
  $result = $receivedTransactions-&gt;leftJoinSub($paidTransactions, 't2', function ($join) {
      $join-&gt;on('t1.to', 't2.account_id');
  })-&gt;selectRaw('totalPaid, (COALESCE(sum(t1.amount),0) - COALESCE(totalPaid,0)) as balance');
  </code></pre>
  <p>The big problem is it doesn't give me the 3rd row in the desired result table, because I don't have full join and I don't get the result from the right table which doesn't have corresponding row in the join so <code>null</code>.</p>
  <p>this is what I get:</p>
  <pre><code>CURRENT QUERY RESULT
  +---+------------+---------------+-----------+---------+
  | # | account_id | totalReceived | totalPaid | balance |
  +---+------------+---------------+-----------+---------+
  | 1 | 1          | 600           | 250       | 350     |
  +---+------------+---------------+-----------+---------+
  | 2 | 2          | 250           | 150       | 100     |
  +---+------------+---------------+-----------+---------+
  | 4 | 4          | 50            | 0         | 50      |
  +---+------------+---------------+-----------+---------+
  </code></pre>
  <p>I don't get the account_id <code>3</code> which was involved in giving account <code>1</code> the transaction no.1</p>
  <p>Any help would be really appreciated. It doesn't matter if it is in Raw MYSQL format or laravel query builder. Thank you very much.</p>