How to Switch Table Dynamically in Laravel Eloquent
Assuming that we have a multitenanted app where each user has its own table, there could be a need to switch table dynamically during query for different users.
There are 2 scenarios you need to take note of when switching table dynamically.
1. Getting Results
Normally to query we would do as such:
$results = \App\Models\Record::query() ->where('sku', '1234') ->get();
Now if you have multiple
records table like
records_for_user_2 etc, your Eloquent query can be updated as such to select different table dynamically:
$results = (new \App\Models\Record) ->setTable('records_for_user_2') ->newQuery() ->where('sku', '1234') ->get();
The results returned will be a collection of
\App\Models\Record objects so you should be able to access relationships, accessors and mutators etc just like a normal
Record object, which is great!
newQuery() is optional. Your queries should work without it in most cases. However recently in an advanced sorting use case my codes didn't work without it. So if you are having similar issues I would recommend putting it in.
But if you try to insert a new record with this method, you'll get an error immediately.
2. Inserting Records
This is because, when we access the static methods, a new model is being created in the background and it ignores the new table that we set.
To work around this issue, we will need to save our data via non-static means.
\App\Models\Record::create($data); // Or (new \App\Models\Record) ->setTable('records_for_user_2'); ->create($data);
We need to insert new records as such:
(new \App\Models\Record) ->setTable('records_for_user_2'); ->fill($data) ->save();
Now you should be able to save your data into your desired tables dynamically upon request!