So I'm trying to get a Laravel Eloquent query to work. Here's the scenario:
1) I have one table called c5_wall_route_taxonomies That looks like the following:
---------------------------------------------
| ID | TYPE | CREATED_ON |
---------------------------------------------
| 2 | 'country' | 2014-08-12 12:12:12 |
---------------------------------------------
Where ID is an auto-incrementing integer, TYPE is an enum, and CREATED_ON is a pretty standard timestamp.
2) I have another table called c5_wall_route_taxonomy_options that looks like the following:
-----------------------------------------------------------------
| ID | WALL_ROUTE_TAXONOMY_ID | META_KEY | META_VALUE |
-----------------------------------------------------------------
| 1 | 2 | 'label' | 'Malaysia' |
| 2 | 2 | 'code' | 'MY' |
-----------------------------------------------------------------
And I'm trying to get the fields from the c5_wall_route_taxonomy table along with an extra 'country_label' field.
$result = DB::table('wall_route_taxonomies')
->join('wall_route_taxonomy_options as country', function($join) {
$join->on('wall_route_taxonomies.id', '=', 'country.wall_route_taxonomy_id')
->where('country.meta_key', '=', 'label');
})->get();
Note that my table prefix is c5_.
Laravel tells me that it's unable to find c5_country.wall_route_taxonomy_id.
Now in this scenario, I could always just opt not to use the alias, and just use wall_route_taxonomy_options.wall_route_taxonomy_id instead, but what if I wanted to get both the label and the code from the options table as individual fields in my output?
Surely there's a way to implement this in Laravel?