Dynamically switching tables for model

Need Help
#1

Hi all,

I’m trying to make my SiteContact model use different database tables depending on the associated Site. Each Site has its own contacts table (e.g., abc_contacts, xyz_contacts).

My goal is to display a list of all contacts within a specific Site’s table using a relation. I’ve tried event binding, scopes, etc., but my model keeps using the default ‘site_contacts’ table.

Site::extend(function ($model) {
    $model->bindEvent('model.afterFetch', function () use ($model) {
        if ($model->contacts) {
            $model->contacts['table'] =  $model->code . '_contacts';
        }
    });
});

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'site_contacts.site_id' in 'where clause' (Connection: mysql, SQL: select * from `q4balb3w_contacts` where `site_contacts`.`site_id` = 3 and `site_contacts`.`site_id` is not null)

So based on the error message, it appears filtering by ‘site_id’ is causing the problem. Is it possible to disable it?

Has anyone done this before? Any tips or solutions would be amazing!

#2

I’m not 100% sure what you mean, the example code is a touch confusing - it mentions SiteContact but extends Site… However, based on your description, something like this?

SiteContact::extend(function($model) {
    $model->setTable(\Site::getSiteCodeFromContext() . '_contacts');
});
#3

Hey, thanks for the quick response. To simplify matters, I want to create a hasMany relationship on the Site model where I dynamically define the table, and the key is not necessary because each table is unique.

Something like this:

public $hasMany = [
    'contacts' => [
        SiteContact::class,
        'table' => 'abcreate_almalio_' . $this->code . '_contacts',
    ],
];

Is it possible?

#4

I think you might have to bypass standard relationship where the keys are used.

Maybe you can create a function that will mimic relationship, like:

<?php

use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class Site extends Model
{

    public function contacts(): Collection
    {
        // Assuming the table name is dynamically determined based on $this->tablePrefix
        $tableName = $this->tablePrefix . '_contacts';

        // Retrieve contacts directly from the database without using keys
        // Adjust this query according to your database structure
        return DB::table($tableName)->get();
    }
}
#5

This is one of the solutions I tried. I also tried Laravel relationships and custom relationships in the HasRelationship trait. These solutions are fine, but i want to display list of contacts under Site model as relationship in administration and that’s the main problem.

#6

And creating a new table “author_plugin_sites_contacts” is not a solution (and creating author_plugin_contacts)?

if your table q4balb3w_contacts are for site_id = 3, you could transfer them into the new tables.

#7

Of course, a many-to-many relationship between Site and SiteContact would be great (actually, I am using it right now), but there are some legal issues for my case, and I need to change it so that every site has a separate table for contacts.

#8

Hey @Webess

Try using the model.afterRelation event, this is called when a relation instance is created.

\System\Models\SiteDefinition::extend(function($model) {
    $model->hasMany['contacts'] = SiteContact::class;

    $model->bindEvent('model.afterRelation', function($name, $related) use ($model) {
        if ($name === 'contacts') {
            $related->setTable("abcreate_almalio_{$model->code}_contacts");
        }
    });
});

I hope this helps.

3 Likes
#9

Thanks @daft, this is exactly what I needed.