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
– t1gor, Commented Jul 28, 2017 at 9: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
– J Foley, Commented Jul 28, 2017 at 9:51
And yes schema1 and schema2 are different databases, need to remain like this due to existing business logic. — J Foley
– J Foley, Commented Jul 28, 2017 at 9:51
Possible duplicate of laravel BelongsTo relationship with different databases not working — t1gor
– t1gor, Commented Jul 28, 2017 at 9:59
I think this problem is best solved with polymorphism: let multiple sub-models extend 1 base model based on schema2 — online Thomas
– online Thomas, Commented Jul 28, 2017 at 11:35

J Foley · Accepted Answer · 2017-07-28 10:40:16Z

1

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

answered Jul 28, 2017 at 10:40

J Foley

1,0971 gold badge20 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

t1gor · Accepted Answer · 2017-07-28 09:59:07Z

0

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

answered Jul 28, 2017 at 9:59

t1gor

1,28212 silver badges25 bronze badges

1 Comment

J Foley Over a year ago

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

Collectives™ on Stack Overflow

Laravel Eloquent, multiple schema query

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related