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_1
and 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!
Note that 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.
Instead of:
\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!