Laravel Eloquent, multiple schema query

Question

I have the below query (simplified):

$q = ModelOne::with('relation_one', 'relation_two')
    ->whereHas('relation_three', function ($q) {
        $q->where('object', 'Obj1');
    })
    ->whereHas('relation_four', function ($q) {
        $q->where('object', 'Obj2');
    })
    ->get();`

It loads the relation_one and relation_two relationships fine, I also need to load another relationship per row, either relation_three or relation_four depending on the value of ModelOne->object.

The issue I am having is that ModelOne is from schema1 and the tables used in relation_three & relation_four are from schema2.

Both models are set up correct with their individual protected $connection and protected $table variables.

The error I am recieving is that the tables for relationship_three or relationship_four does not exist as the sub-query is checking the wrong schema.

Can anyone suggest how to fix this? Have had a look through the docs but couldn't find a solution.

You could just load both relations and use a correct one in your logic. Also, what is `schema1` & `schema2`? Different databases? — t1gor, Jul 28 '17 at 09:41
I will try loading both and see if I can get the results the way I need, might cause an issue if both relationships are loaded due to both tables containing the same ID's but will need to play around with it so see. — J Foley, Jul 28 '17 at 09:51
And yes `schema1` and `schema2` are different databases, need to remain like this due to existing business logic. — J Foley, Jul 28 '17 at 09:51
Possible duplicate of [laravel BelongsTo relationship with different databases not working](https://stackoverflow.com/questions/32422593/laravel-belongsto-relationship-with-different-databases-not-working) — t1gor, Jul 28 '17 at 09:59
I think this problem is best solved with `polymorphism`: let multiple sub-models extend 1 base model based on *schema2* — online Thomas, Jul 28 '17 at 11:35

score 1 · Accepted Answer · answered Jul 28 '17 at 10:40

Maybe not the most elegant solution but got this working by calling relationships and joining as follows:

$q = ModelOne::with('relation_one', 'relation_two')
    ->with(['relation_three' => function ($q) {
        $q->leftJoin(
            'schema1.model_one',
            'table_three.id',
            '=',
            'model_one.object_id'
        )
        ->where('object', 'Obj1');
    }])
    ->with(['relation_four' => function ($q) {
        $q->leftJoin(
            'schema1.model_one',
            'table_four.id',
            '=',
            'model_one.object_id'
        )
        ->where('object', 'Obj2');
    }])
    ->get();`

If anyone can suggest some improvements or a more efficient way to do this please let me know

score 0 · Answer 2 · answered Jul 28 '17 at 09:59

I would suggest separating the different databases relations to different fields, at least. This way you can then load both (as suggested in comments) and differentiate the logic within controller/model code.

Also, I guess you'll need to define the connection name on the Model level, if not done yet:

class Model_Two_Relation {
    protected $connection = 'your-database-name-from-config';
}

You also might want to specify the connection within the relation join condition:

$q = ModelOne::with('relation_one', 'relation_two')
    ->whereHas('relation_three', function ($q) {
        $q->from('resources.one')->where('object', 'Obj1');
    })
    ->whereHas('relation_four', function ($q) {
        $q->from('resources.two')->where('object', 'Obj2');
    })
    ->get();

Links: http://fideloper.com/laravel-multiple-database-connections

Connections are already defined at model level. Error thrown when specifying connection in a join like that, `Call to undefined method Illuminate\Database\Query\Builder::resource()`. Will have a read over the article linked, thank you — J Foley, Jul 28 '17 at 10:09

Laravel Eloquent, multiple schema query

2 Answers2