title: group by result not exist in join other tabel laravel?
body: <p>i have two table and one pivot table.
  settlement table is this:</p>
  <pre><code>        $table-&gt;ulid();
          $table-&gt;string('track_id', 36);
          $table-&gt;string('payee_user_id', 36);
          $table-&gt;string('terminal_type', 36)-&gt;nullable();
          $table-&gt;string('terminal_provider', 36)-&gt;nullable();
          $table-&gt;string('terminal_psp', 36)-&gt;nullable();
          $table-&gt;string('terminal_id', 36)-&gt;nullable();
          $table-&gt;string('acceptor_id', 36)-&gt;nullable();
          $table-&gt;string('terminal_no', 36)-&gt;nullable();
          $table-&gt;string('account_bank_id', 36)-&gt;nullable();
          $table-&gt;string('account_id', 36)-&gt;nullable();
          $table-&gt;string('account_deposit', 36)-&gt;nullable();
          $table-&gt;string('account_iban', 36)-&gt;nullable();
          $table-&gt;integer('count')-&gt;nullable();
          $table-&gt;decimal('amount_settle', 15, 2);
          $table-&gt;decimal('amount_wage', 15, 2);
          $table-&gt;timestamps(6);
  </code></pre>
  <p>transaction table is this</p>
  <pre><code>        $table-&gt;ulid();
          $table-&gt;string('payee_user_id', 36);
          $table-&gt;string('type', 36);
          $table-&gt;string('terminal_id', 36);
          $table-&gt;string('terminal_no', 36);
          $table-&gt;string('terminal_type', 36);
          $table-&gt;string('acceptor_id', 36);
          $table-&gt;string('terminal_psp', 36);
          $table-&gt;string('terminal_provider', 36);
          $table-&gt;decimal('amount_settle', 15, 2);
          $table-&gt;decimal('amount_wage', 15, 2);
          $table-&gt;string('account_id', 36)-&gt;nullable();;
          $table-&gt;string('account_bank_id', 36)-&gt;nullable();;
          $table-&gt;string('account_deposit', 26)-&gt;nullable();
          $table-&gt;string('account_iban', 26)-&gt;nullable();
          $table-&gt;string('wallet_id', 36)-&gt;nullable();
          $table-&gt;timestamp('paid_at', 6);
  </code></pre>
  <p>settlements_transactions table is this:</p>
  <pre><code>        $table-&gt;string('transaction_id')-&gt;index();
          $table-&gt;string('settlement_id')-&gt;index();
          $table-&gt;foreign('transaction_id', 'fk_transaction_id')
              -&gt;on('transactions')-&gt;references('id');
  
          $table-&gt;foreign('settlement_id', 'fk_settlement_id')
              -&gt;on('settlements')-&gt;references('id');
  </code></pre>
  <p>I am inserting group by of transaction in settlement table If I have not saved before.</p>
  <pre><code>$transactions = DB::table('transactions')
          -&gt;where('paid_at', '&lt;', $date)
          -&gt;where('terminal_type', Transaction::PRIVATE)
          -&gt;where('terminal_provider', Transaction::SHAPARAK)
          -&gt;distinct()-&gt;selectRaw(
              'account_iban,
               account_deposit,
               account_bank_id,
               payee_user_id,
               account_id,
               terminal_psp,
               terminal_id,
               terminal_no,
               acceptor_id,
               account_id,
               SUM(amount_settle) as amount_settle,
               SUM(amount_wage) as amount_wage,
               COUNT(id) as count'
          )-&gt;groupBy(
              'payee_user_id',
              'account_id',
              'account_iban',
              'terminal_psp',
              'terminal_no',
              'terminal_id',
              'acceptor_id',
              'account_id',
              'account_deposit',
              'account_bank_id',
          )-&gt;orderBy('payee_user_id')
           -&gt;orderBy('account_id')
           -&gt;orderBy('terminal_psp')
          -&gt;orderBy('terminal_id')
          -&gt;orderBy('account_id')
          -&gt;orderBy('account_deposit')
          -&gt;orderBy('account_bank_id')
          -&gt;orderBy('account_iban')
          -&gt;orderBy('terminal_no')
          -&gt;orderBy('acceptor_id')
          -&gt;get();
  
        foreach ($transactions as $transaction) {
  
          $exist = DB::table('settlements')
              -&gt;where('payee_user_id', $transaction-&gt;payee_user_id)
              -&gt;where('account_id', $transaction-&gt;account_id)
              -&gt;where('account_deposit', $transaction-&gt;account_deposit)
              -&gt;where('terminal_id', $transaction-&gt;terminal_id)
              -&gt;where('terminal_psp', $transaction-&gt;terminal_psp)
              -&gt;where('account_bank_id', $transaction-&gt;account_bank_id)
              -&gt;where('terminal_provider', Transaction::SHAPARAK)
              -&gt;where('terminal_type', Transaction::PRIVATE)
              -&gt;where('settlements.created_at', '&gt;=', Carbon::today())
              -&gt;join('settlement_statuses', 'settlement_statuses.settlement_id', 'settlements.id')
              -&gt;applySettlementLastStatusSubJoin('settlement_statuses')
              -&gt;applySettlementLastAccepted('settlements')
              -&gt;exists();
  
          if ($exist) {
              continue;
          }
  
        //insert in settlements table
  
     }
  
       
  </code></pre>
  <p>I do not want to check exist in the loop. i need to get transactions group by result if not exist in settlement table.</p>