i have two table and one pivot table. settlement table is this:
$table->ulid();
$table->string('track_id', 36);
$table->string('payee_user_id', 36);
$table->string('terminal_type', 36)->nullable();
$table->string('terminal_provider', 36)->nullable();
$table->string('terminal_psp', 36)->nullable();
$table->string('terminal_id', 36)->nullable();
$table->string('acceptor_id', 36)->nullable();
$table->string('terminal_no', 36)->nullable();
$table->string('account_bank_id', 36)->nullable();
$table->string('account_id', 36)->nullable();
$table->string('account_deposit', 36)->nullable();
$table->string('account_iban', 36)->nullable();
$table->integer('count')->nullable();
$table->decimal('amount_settle', 15, 2);
$table->decimal('amount_wage', 15, 2);
$table->timestamps(6);
transaction table is this
$table->ulid();
$table->string('payee_user_id', 36);
$table->string('type', 36);
$table->string('terminal_id', 36);
$table->string('terminal_no', 36);
$table->string('terminal_type', 36);
$table->string('acceptor_id', 36);
$table->string('terminal_psp', 36);
$table->string('terminal_provider', 36);
$table->decimal('amount_settle', 15, 2);
$table->decimal('amount_wage', 15, 2);
$table->string('account_id', 36)->nullable();;
$table->string('account_bank_id', 36)->nullable();;
$table->string('account_deposit', 26)->nullable();
$table->string('account_iban', 26)->nullable();
$table->string('wallet_id', 36)->nullable();
$table->timestamp('paid_at', 6);
settlements_transactions table is this:
$table->string('transaction_id')->index();
$table->string('settlement_id')->index();
$table->foreign('transaction_id', 'fk_transaction_id')
->on('transactions')->references('id');
$table->foreign('settlement_id', 'fk_settlement_id')
->on('settlements')->references('id');
I am inserting group by of transaction in settlement table If I have not saved before.
$transactions = DB::table('transactions')
->where('paid_at', '<', $date)
->where('terminal_type', Transaction::PRIVATE)
->where('terminal_provider', Transaction::SHAPARAK)
->distinct()->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'
)->groupBy(
'payee_user_id',
'account_id',
'account_iban',
'terminal_psp',
'terminal_no',
'terminal_id',
'acceptor_id',
'account_id',
'account_deposit',
'account_bank_id',
)->orderBy('payee_user_id')
->orderBy('account_id')
->orderBy('terminal_psp')
->orderBy('terminal_id')
->orderBy('account_id')
->orderBy('account_deposit')
->orderBy('account_bank_id')
->orderBy('account_iban')
->orderBy('terminal_no')
->orderBy('acceptor_id')
->get();
foreach ($transactions as $transaction) {
$exist = DB::table('settlements')
->where('payee_user_id', $transaction->payee_user_id)
->where('account_id', $transaction->account_id)
->where('account_deposit', $transaction->account_deposit)
->where('terminal_id', $transaction->terminal_id)
->where('terminal_psp', $transaction->terminal_psp)
->where('account_bank_id', $transaction->account_bank_id)
->where('terminal_provider', Transaction::SHAPARAK)
->where('terminal_type', Transaction::PRIVATE)
->where('settlements.created_at', '>=', Carbon::today())
->join('settlement_statuses', 'settlement_statuses.settlement_id', 'settlements.id')
->applySettlementLastStatusSubJoin('settlement_statuses')
->applySettlementLastAccepted('settlements')
->exists();
if ($exist) {
continue;
}
//insert in settlements table
}
I do not want to check exist in the loop. i need to get transactions group by result if not exist in settlement table.
When you adjust the transaction selection so that it is not necessary to verify whether an entry already exists in the settlements. The principle is to do a "left join" followed by a filter for values that do not have a corresponding entry in the settlements table (->whereNull('s.id')).
The problem with this solution can be performance, depending on several factors. In any case, this should not be a problem if the indexes are used correctly.
$transactions = DB::table('transactions AS t')
->leftJoin('settlements AS s', function ($join) {
$join
->on('s.payee_user_id', '=', 't.payee_user_id')
->on('s.account_id', '=', 't.account_id')
->on('s.account_deposit', '=', 't.account_deposit')
->on('s.terminal_id', '=', 't.terminal_id')
->on('s.terminal_psp', '=', 't.terminal_psp')
->on('s.account_bank_id', '=', 't.account_bank_id')
->where('s.terminal_provider', '=', Transaction::SHAPARAK)
->where('s.terminal_type', '=', Transaction::PRIVATE)
->where('s.created_at', '>=', Carbon::today());
})
->whereNull('s.id')
->where('t.paid_at', '<', $date)
->where('t.terminal_type', Transaction::PRIVATE)
->where('t.terminal_provider', Transaction::SHAPARAK)
->distinct()->selectRaw(
't.account_iban,
t.account_deposit,
t.account_bank_id,
t.payee_user_id,
t.account_id,
t.terminal_psp,
t.terminal_id,
t.terminal_no,
t.acceptor_id,
t.account_id,
SUM(t.amount_settle) as amount_settle,
SUM(t.amount_wage) as amount_wage,
COUNT(t.id) as count'
)->groupBy(
't.payee_user_id',
't.account_id',
't.account_iban',
't.terminal_psp',
't.terminal_no',
't.terminal_id',
't.acceptor_id',
't.account_id',
't.account_deposit',
't.account_bank_id',
)->orderBy('t.payee_user_id')
->orderBy('t.account_id')
->orderBy('t.terminal_psp')
->orderBy('t.terminal_id')
->orderBy('t.account_id')
->orderBy('t.account_deposit')
->orderBy('t.account_bank_id')
->orderBy('t.account_iban')
->orderBy('t.terminal_no')
->orderBy('t.acceptor_id')
->get();
EDIT: The same solution but with relation table usage..
<?php
$transactions = DB::table('transactions AS t')
->leftJoin('settlements_transactions AS st', function ($join) {
$join
->on('st.transaction_id', '=', 't.id')
// these "where" conditions are maybe redundant
->where('s.terminal_provider', '=', Transaction::SHAPARAK)
->where('s.terminal_type', '=', Transaction::PRIVATE)
->where('s.created_at', '>=', Carbon::today());
})
->whereNull('st.transaction_id')
->where('t.paid_at', '<', $date)
->where('t.terminal_type', Transaction::PRIVATE)
->where('t.terminal_provider', Transaction::SHAPARAK)
->distinct()->selectRaw(
't.account_iban,
t.account_deposit,
t.account_bank_id,
t.payee_user_id,
t.account_id,
t.terminal_psp,
t.terminal_id,
t.terminal_no,
t.acceptor_id,
t.account_id,
SUM(t.amount_settle) as amount_settle,
SUM(t.amount_wage) as amount_wage,
COUNT(t.id) as count'
)->groupBy(
't.payee_user_id',
't.account_id',
't.account_iban',
't.terminal_psp',
't.terminal_no',
't.terminal_id',
't.acceptor_id',
't.account_id',
't.account_deposit',
't.account_bank_id',
)->orderBy('t.payee_user_id')
->orderBy('t.account_id')
->orderBy('t.terminal_psp')
->orderBy('t.terminal_id')
->orderBy('t.account_id')
->orderBy('t.account_deposit')
->orderBy('t.account_bank_id')
->orderBy('t.account_iban')
->orderBy('t.terminal_no')
->orderBy('t.acceptor_id')
->get();