Using a join() method in Laravel 5.1?
I'm using Laravel 5.1 and I want to run a model that can join two tables together.
So I have a Market and a People model plus also a TableRelation to link tables. The first two are just IDs and names while the TableRelation has the following schema
- id
- table_1_id
- table_1_model
- table_2_id
- table_2_model
(Note: this isn't to exclusively link the two tables mentioned but any number of tables in the DB)
Now I want to run the following query:
SELECT
tr.*
FROM
market
INNER JOIN
table_relations AS tr ON ((
tr.table_1_id = market.id
AND tr.table_1_model = 'App\\Market'
) OR (
tr.table_2_id = market.id
AND tr.table_2_model = 'App\\Market'
))
But I want to be able to use it in the context of Eloquent so I can run code such as \App\Market::withRelation()->get(); or \App\Market::whereName('Clothing')->withRelation()->get(); (scopeWithRelation() being in a trait) but in an efficient manner. At the moment I would need to run three queries:
- Query the model to get the results of selected rows
- Use the results to then query TableRelation to get the model I want to query
- Query the models based on results from TableRelation
But it would be more efficient if I could concatenate 1 and 2 like in my SQL example. I tried it with the following:
$query->join('table_relations', function($join) {
$join->on('table_relations.table_1_id', '=', 'markets.id');
$join->on('table_relations.table_1_model', '=', 'App\\Market');
})->select('table_relations.*');
But I believe join() was deprecated in version 5.* so I'm at a bit of a loss and feel there are only two options that are to loose the ability to chain Eloquent's build methods, or just run three queries every-time.
Any help would be amazing!
Please or to participate in this conversation.