0

I have two table.

this is transaction table:

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

this is status table:

        $table->ulid();
        $table->string('transaction_id');
        $table->string('status', 36);
        $table->string('description', 36)->nullable();

        $table->foreign('transaction_id')
            ->on('transactions')->references('id');

        $table->timestamp('created_at', 6)->useCurrent()->index();

any transactions has many statuses.I need to get transactions whose latest status is successful.performance is very important. I do not want eloquent.

mohammad nabipour
  • 77
  • 1
  • 7

1 Answers1

0

You are referencing id but your schema shows you are using ulid. If it's id you are using you can change ulid to id below.

    DB::table('transactions')->selectRaw('transactions.ulid as id, transactions.bank_id as bank_id')
        ->leftJoin('statuses', 'transactions.ulid', '=', 'statuses.transaction_id')
        ->where('statues.status', 'successful')
        ->get();
Benart
  • 1
  • 1
  • 1