Table Alias not getting right for softDelete on Model

[parmonov98]

• Laravel Version:

• PHP Version: 8.0
• Database Driver & Version: MySql

Description:

I enabled softDelete in Model using trait. and the tale has deleted_at column.
This query
EmployeeTransaction::query()->from("employee_transactions as et")
->leftJoin('employees as e', 'e.id', '=', 'et.employee_id')
->whereRaw("DATE_FORMAT(et.date, '%Y-%m-%d') >= '$this->from_date'")
->whereRaw("DATE_FORMAT(et.date, '%Y-%m-%d') <= '$this->to_date'")
->groupBy(DB::raw('et.employee_id'))
->select(
DB::raw("e.id as employee_id"),
DB::raw("(SELECT SUM(et.earning_amount) - SUM(et.payment_amount) from employee_transactions as et where et.employee_id = e.id GROUP BY et.employee_id) as left_amount"),
DB::raw("CONCAT(e.first_name, '', e.last_name) as name"),
DB::raw("SUM(et.earning_amount) as earning_amount"),
DB::raw("SUM(et.payment_amount) as payment_amount"),
DB::raw("SUM(earning_amount - payment_amount) as difference_amount"),
DB::raw("et.date as date")
)

It throws an error:

Steps To Reproduce:

create a eloquent query using model and change table name using from or fromRaw

[gguney]

Can you also share employee_transactions table columns?

Did you add softDeletes() to your migration like:

        Schema::create('employee_transactions', function (Blueprint $table) {
            $table->id();
            ...
            $table->softDeletes();
        });

[parmonov98]

[krkarel]

Still an issue in 2024...
"laravel/framework": "^10.9"

[cgarciagarcia]

yeah, could you watch the new post here #52294

[macropay-solutions]

Raw queries should be avoided when possible.

Please try this:

EmployeeTransaction::query()->setModel(($et = new EmployeeTransaction())->setTable($et->getTable() . ' as et'))
->leftJoin('employees as e', 'e.id', '=', 'et.employee_id')
->whereRaw("DATE_FORMAT(et.date, '%Y-%m-%d') >= '$this->from_date'")
->whereRaw("DATE_FORMAT(et.date, '%Y-%m-%d') <= '$this->to_date'")
->groupBy(DB::raw('et.employee_id'))
->select(
DB::raw("e.id as employee_id"),
DB::raw("(SELECT SUM(et.earning_amount) - SUM(et.payment_amount) from employee_transactions as et where et.employee_id = e.id GROUP BY et.employee_id) as left_amount"),
DB::raw("CONCAT(e.first_name, '', e.last_name) as name"),
DB::raw("SUM(et.earning_amount) as earning_amount"),
DB::raw("SUM(et.payment_amount) as payment_amount"),
DB::raw("SUM(earning_amount - payment_amount) as difference_amount"),
DB::raw("et.date as date")
)

[cgarciagarcia]

I wrote a solution fot this. here you are the PR, is a rudimentary way to resolve this problem. Let's see what happen.