laravel - How to use Eloquent model relationships with dynamically generated table names - Stack Overflow

admin管理员组

文章数量:1389754

This feels like a rather common use-case. I have a multi-tenant application. And I have more than one database. The primary database has an accounts table. A second database has generated tables for every account, with table names like account_[ACCOUNT_ID]_events. (Tens of thousands of events per account, hundreds of client accounts, a single table isn't practical.)

With Laravel, I set up multiple connections in the config and create models for my tables. Well..

1. I want to create an Event model tied to its *_events table. I can set protected $connection = 'events_db';, but how do I dynamically set the table name? I know I can (new Event)->setTable('account_'.$account->id.'_events'), but when and where would I do that?

2. I mostly want the model in order to use relationships. I want to do get $account->events() and return an Eloquent Relation that can be further queried.

I realize I can get around this question by using basic DB query builders, but I'd really love to use Eloquent, as the events table has it's own foreign keys, but again, each table name is generated from the account ID.

This feels like a rather common use-case. I have a multi-tenant application. And I have more than one database. The primary database has an accounts table. A second database has generated tables for every account, with table names like account_[ACCOUNT_ID]_events. (Tens of thousands of events per account, hundreds of client accounts, a single table isn't practical.)

With Laravel, I set up multiple connections in the config and create models for my tables. Well..

1. I want to create an Event model tied to its *_events table. I can set protected $connection = 'events_db';, but how do I dynamically set the table name? I know I can (new Event)->setTable('account_'.$account->id.'_events'), but when and where would I do that?

2. I mostly want the model in order to use relationships. I want to do get $account->events() and return an Eloquent Relation that can be further queried.

I realize I can get around this question by using basic DB query builders, but I'd really love to use Eloquent, as the events table has it's own foreign keys, but again, each table name is generated from the account ID.

• laravel
• eloquent-relationship

Share Improve this question Follow edited Mar 13 at 17:57 Phil Tune asked Mar 13 at 15:17 Phil TunePhil Tune 3,37533 gold badges2727 silver badges5151 bronze badges 3

• if you have a multi-tenant application with multiple databases, why do you need to also have prefix each table with your tenant? when you can just name the database according to your tenant – kris gjika Commented Mar 13 at 15:43
• @krisgjika That option was discussed. With over 500 clients the maintenance becomes an issue. Any new migrations have to be batched and automated tests run on each client. Manual reviews would be impractical, which includes statistics and demographics gathering. It'd certainly solve other issues, but it breaks so many other processes. – Phil Tune Commented Mar 13 at 17:42
• As with so much of our life as engineers, I've been converting an old old legacy system to Laravel, so some existing schemas will be harder to upgrade than others. – Phil Tune Commented Mar 13 at 17:45

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

I liked my one solution enough I decided to write it down for posterity.

When I'm selecting events, I happen to have the account_id as a route param. So adding this to the Event model solves at least one conundrum:

class Event extends Model  
{  
    protected $connection = 'events_db';  
  
    public function getTable():string  
    {  
        return 'account_' . request()->route('account')?->id . '_events';  
    }  
}

...

$events = Event::all(); // only gives me events from the selected account

This, of course, only works if your request or session happens to be holding the variable to calculate your table name. (All of our users can access multiple accounts, so we have them select the account they want to work with.) If your authenticated user is assigned to a single account, you could generate this from Auth::user().

Now there's no simple way to have something like a Account::find($id)->events relationship (that I've found at least), so I can't, say, loop through accounts and echo their events_count. Even doing that manually would create an n+1 series that wouldn't be performant.

本文标签： laravelHow to use Eloquent model relationships with dynamically generated table namesStack Overflow