[Proposal] Database table alias similar to ZF1

[formigone]

tl;dr

Allow for $table (as in DB::table($table) or DB::table(...)->join($table)) to be a string (as it currently is) or an array, as in ['table_alias' => $table].

Pain points

I'm working on a project where instead of typing table names by hand, we use constants:

$records = DB::table(Model::TABLE_SHOES)->(...);

This works well until we start to join tables and work with aliases to simplify stuff:

$records = DB::table(Model::TABLE_SHOES . ' as shoes')
   ->join(Model::TABLE_SOCKS . ' as socks', 'socks.id', '=', 'shoes.socks_id')->(...);

Having to concatenate those strings (and remembering to add a leading blank space) is not as clean as we'd like. The following proposal would clean up this use case.

The proposal

Taking inspiration from Zend Framework 1's DB abstraction, it would be nice to allow for programmatically specifying table aliases, as in:

$records = DB::table(['shoes' => Model::TABLE_SHOES])
   ->join(['socks' => Model::TABLE_SOCKS], 'socks.id', '=', 'shoes.socks_id')->(...);

This would eliminate the need to concatenate strings in order to do <table> as <alias>.

By the way, I'm a long time ZF1 user, with prior experience with CakePHP and Slim, and more recently, about a year of deep involvement with Silex. About a month ago I joined a team starting a new project in Lumen. I really like what you're doing with it. Great work!

[ragingdave]

Interesting idea, might actually incorporate the idea a bit in my current project. One specific point is how would you handle prefixes then? Right or wrong I have a migration project that is aliasing the crap out of tables to be able to migrate properly keyed data (and eliminate some bad data in the process). As a part of this, I'm breaking up a multi-app database into at least a few separate ones and need to alias with the prefix. Currently that's simple enough like:

DB::table($db.'.'.prefix.'table as t1')

I know, I know for the love of god why am I doing that....because shhh..

But anyway, I would think that any change like this should have feature parity to what is capable now so being able to have a prefix and potentially, as dirty as it is, a database specified should be a part of it.

On the other hand/Devil's advocate......My question might be why not just use Eloquent instead and have it do most of the work for you instead of having to result to the Query Builder.....I'm guessing performance reasons, but if it's not then perhaps Eloquent would be of greater help.