group by result not exist in join other tabel laravel?

Question

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.

Martin Osusky · Accepted Answer · 2021-08-12T15:33:41.707

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();

I think your answer is correct, but you ignored settlements_transactions table. — mohammad, Aug 12 '21 at 11:36
It is possible, I did not realize that "// insert in settlements table" there is also a relation insert. It will be even easier "leftJoin". I will edit and update the answer. — Martin Osusky, Aug 12 '21 at 15:29

group by result not exist in join other tabel laravel?

1 Answers1