Framework: Table alias being forgotten when joining with DB::raw

[kadivadavidak]

• Laravel Version: 5.6.23
• PHP Version: 7.1.17
• Database Driver & Version: mysql Ver 15.1 Distrib 10.0.34-MariaDB

Description:

I have several queries that I am working with that need (I'd like) to be nested. I'd also prefer to use table aliases as this makes my code cleaner and easier to read, imo.

However, when using ::from('table as t') on my base model to add the alias, the alias is forgotten later on when it tries to do where table.deleted_at is null. In this example, table should have the as t alias given in the from, but is reverting back to the base table name, leaving a conflict, and the following error:

"Illuminate/Database/QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'table.deleted_at' in 'where clause' (SQL: select * from `table` as `t` left join (select * from `table_2` as `t2`) as t2 on `t2`.`table_id` = `t`.`id` where `table`.`deleted_at` is null)'"

EDIT: As mentioned in the comments, both table and table_2 are using SoftDeletes. Thanks @staudenmeir for pointing that out.

Steps To Reproduce:

1. Make a simple query:
   $t2 = Table2::from('table_2 as t2')->toSql();

2. Write a second query that nests the above:
   Table::from('table as t')->leftJoin(DB::raw('('.$t2.') as t2'), 't2.table_id', 't.id')->get();

3. Run above.

4. Get following error:
   "Illuminate/Database/QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'table.deleted_at' in 'where clause' (SQL: select * from `table` as `t` left join (select * from `table_2` as `t2`) as t2 on `t2`.`table_id` = `t`.`id` where `table`.`deleted_at` is null)'"

`table`.`deleted_at` in the above error should be `t`.`deleted_at`, thus the conflict and error.

[kadivadavidak]

Possibly related to https://github.com/laravel/ideas/issues/1206?

[staudenmeir]

You should explicitly mention that the Table model uses SoftDeletes, that's not obvious from your description. And please fix the closing bracket of DB::raw().

[staudenmeir]

This also affects methods like whereKey().

Unfortunately, a fix would require breaking changes in multiple places.

As a possible workaround, you can use a custom subclass for alias queries:

class TableAlias extends Table {
    const DELETED_AT = 't.deleted_at';
    protected $table = 'table as t';
}

TableAlias::leftJoin(DB::raw('('.$t2.') as t2'), 't2.table_id', 't.id')->get();