Framework: Eager loading from 2 schemas with dynamic table names

[032mix]

Hello everyone,

I am having trouble making this work. I have these two models:

class Prize extends Model
{
    protected $connection = 'mysql';
    protected $table = 'campaign_prizes';

    public function campaign_prizes()
    {
        return $this->hasMany(CampaignPrize::class, 'uname', 'key');
    }
}

and

class CampaignPrize extends Model
{
    protected $connection = 'mysql_campaigns';

    public function prize()
    {
        return $this->belongsTo(Prize::class, 'uname', 'key');
    }
}

The models' tables are on different schemas on the same db server. The Prize model's table is constant while the CampaignPrize's table is dynamic( it must change depending on the page ).
I am trying to do something like

public function show(Campaign $campaign)
    {
        return view('single', [
            'prizes' => Prize::with(['campaign_prizes' => function() use ($campaign) {
                 return CampaignPrize::from($campaign->tablesPrefix . '_prizes');
                 }])->get()
        ]);
    }

to get foreach prizes $prize->campaign_prize. Error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'THESECONDSCHEMA.campaign_prizes' doesn't exist (SQL: select * from campaign_prizes where campaign_prizes.uname in (...))`

Is it even possible to get the items from the first schema and eager load in them the items from the second schema when those items in the second schema have a dynamic table name?
If I set a constant table in the PrizeCampaign model and just do a

public function show(Campaign $campaign)
    {
        return view('single', [
            'prizes' => Prize::with('campaign_prizes')->get()
        ]);
    }

everything is fine.
The from() method seems to not do anything at all.

• Laravel Version: 5.6.13
• PHP Version: 7.2.3
• Database Driver: both are mysql

[staudenmeir]

Using multiple connections isn't the problem. You have to set the table on the $query parameter, not return a new query. And you have to change the table in the WHERE constraint:

Prize::with(['campaign_prizes' => function($query) use ($campaign) {
    $table = $campaign->tablesPrefix . '_prizes';
    $query->from($table);           
    $query->getQuery()->getQuery()->wheres[0]['column'] = $table.'.'.$query->getForeignKeyName();
}])->get()