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!