Querying the existence of relationships using multiple MySQL database connections in Laravel 5.2

Question

I deal with the following situation: I have two models: Employee with fields id and name and Telephone with fields id , employee_id and flag . There is a one-to-many relationship between the two models, that is, an employee can have many phones, and a phone can belong to one employee.

 class Employee extends Model { public function telephones() { return $this->hasMany(Telephone::class); } } class Telephone extends Model { public function employee() { return $this->belongsTo(Employee::class); } } 

The Employee model refers to the employees table, which exists in the database schema named mydb1 , and the Telephone model refers to the telephones table, which exists in another database schema named mydb2 .

I want to get only employees with at least one phone of a certain flag loaded using Eloquent and (if possible), and not a query designer

I have tried so far without success:

1) use the whereHas method in the controller

 $employees = Employee::whereHas('telephones', function ($query) { $query->where('flag', 1); //Fetch only the employees with telephones of flag=1 })->with([ 'telephones' => function ($query) { //Eager load only the telephones of flag=1 $query->where('flag', 1); } ])->get(); 

What I'm trying to do here is to first get only employees who have phones with flag = 1, and secondly, to load only these phones, but I get the following request exception due to different db:

Base table or view not found: Table mydb1.telephones does not exist (it is true, phones exist in mydb2)

2) Terrible load with restrictions in the controller

 $employees = Employee::with([ 'telephones' => function ($query) { $query->where('flag', 1); }, ])->get(); 

This method loads phones with flag = 1, but it returns all instances of employees, which I really don't want. I would like to have a collection of only models of employees who have phones with flag = 1, except for models with telephones = []

Answer 1

Given this post , this post and @Giedrius Kiršys answer below, I finally came up with a solution that fits my needs using the following steps:

• create a method that returns a Relation object in Model
• load new relationships in the controller
• filter flag phones! = 1 using the query area in the model

In model Employee

 /** * This is the new relationship * */ public function flaggedTelephones() { return $this->telephones() ->where('flag', 1); //this will return a relation object } /** * This is the query scope that filters the flagged telephones * * This is the raw query performed: * select * from mydb1.employees where exists ( * select * from mydb2.telephones * where telephones.employee_id = employee.id * and flag = 1); * */ public function scopeHasFlaggedTelephones($query, $id) { return $query->whereExists(function ($query) use ($id) { $query->select(DB::raw('*')) ->from('mydb2.telephones') ->where('telephones.flag', $flag) ->whereRaw('telephones.employee_id = employees.id'); }); } 

In the controller

I can now use this elegant ala Eloquent syntax

 $employees = Employee::with('flaggedTelephones')->hasFlaggedTelephones()->get(); 

which reads "Retrieve all employees with marked phones to be downloaded, and then take only employees who have at least one marked phone"

EDIT:

After working with the Laravel database for a while (the current version used in 5.2.39), I realized that whereHas() actually work if the relationship model exists in another database using the from() method, like shown below:

 $employees = Employee::whereHas('telephones', function($query){ $query->from('mydb2.telephones')->where('flag', 1); })->get(); 

Credits @Rob Contreras to indicate the use of the from() method, however it seems that this method requires taking both the database and the table as an argument.

Answer 2

Not sure if this will work, but you can use the from method to specify the database connection in closure:

 $employees = Employee::whereHas('telephones', function($query){ $query->from('mydb2')->where('flag', 1); })->get(); 

Hope this helps

Answer 3

Dirty solution:

Use whereExists and scope for better readability.

In your Employee model, put:

 public function scopeFlags($query, $flag) { $query->whereExists(function ($q) use ($flag) { $q->select(\DB::raw(1)) ->from('mydb2.telephones') ->where('telephones.flag', $flag) ->whereRaw('telephones.employee_id = employees.id'); }); } 

Then modify your query as follows:

 $employees = Employee::flags(1)->get();