Querying Relationship Existence using multiple MySQL database connections in Laravel 5.2

Question

I am dealing with the following situation: I have two models, an Employee with id and name fields and a Telephone with id, employee_id and flag fields. There is also an one-to-many relationship between these two models, that is an employee may have many telephones and a telephone may belong to a single 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 references a table employees that exists in database schema named mydb1, while the Telephone model is related to a telephones table that exists in a different database schema named mydb2.

What I want is to fetch only the employees with at least one telephone of a specific flag eager loaded, using Eloquent and (if possible) not the query builder

What I tried so far without success is:

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 try to do here is first to retrieve only the employees that have telephones with flag=1 and second to eager load only these telephones, but I get the following query exception because of the different db connections used:

Base table or view not found: Table mydb1.telephones doesn't exist (this is true, telephones exists in mydb2)

2) Eager load with constrains in the Controller

$employees = Employee::with([

    'telephones' => function ($query) {

        $query->where('flag', 1);
    },

])->get();

This method eager loads the telephones with flag=1, but it returns all the employee instances, which is not what I really want. I would like to have a collection of only the employee models that have telephones with flag = 1, excluding the models with telephones = []

Just a suggestion, but maybe you could make a [view](http://dev.mysql.com/doc/refman/5.7/en/view-syntax.html) in one of your databases that merges the `employees` and `telephones` then pull from the view in your code. Also [see discussion on linking databases in MySQL](http://stackoverflow.com/questions/1565993/oracle-database-link-mysql-equivalent). — Richard, Apr 08 '16 at 13:45
Did you set protected $connection = 'mydb2'; for telephones model? — SergkeiM, Apr 08 '16 at 13:58
@Froxz yes I do but unfortunately whereHas() does not take it into account — ira, Apr 08 '16 at 15:17

score 2 · Accepted Answer · edited May 23 '17 at 11:52

Taking into account 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 the Model
eager load this new relationship in the Controller
filtered out the telephones of flag != 1 using a query scope in the Model

In Employee model

/**
 * 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

Now I may use this elegant syntax a’la Eloquent

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

which reads like "Fetch all the employees with flagged telephones eager loaded, and then take only the employees that have at least one flagged telephone"

EDIT:

After dealing with the Laravel framework for a while (current version used 5.2.39), I figured, that in fact, whereHas() clauses do work in case of the relationship model exists in a different database using the from() method, as it is depicted below:

$employees = Employee::whereHas('telephones', function($query){

    $query->from('mydb2.telephones')->where('flag', 1);

})->get();

@Rob Contreras credits for stating the use of the from() method, however it looks like the method requires to take both the database and the table as an argument.

score 1 · Answer 2 · answered Apr 08 '16 at 14:05

1

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

$employees = Employee::whereHas('telephones', function($query){

    $query->from('mydb2')->where('flag', 1);

})->get();

Hope this helps

answered Apr 08 '16 at 14:05

Rob

36
3

Thank you for your answer but unfortunately that didn't did the trick. What you proposed actually selects a table named 'db2' which lies in the default database connection 'db1': See the error: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mydb1.mydb2' doesn't exist (SQL: select * from `directorates` where exists (select * from `mydb2` where `telephones`.`directorate_id` = `directorates`.`id` and `flag` = 5)). – ira Apr 08 '16 at 15:27
please see my latest edit, related to the usage of `from` method, thank you – ira Jun 28 '16 at 06:26

score 1 · Answer 3 · answered Apr 08 '16 at 17:44

1

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 like so:

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

answered Apr 08 '16 at 17:44

Giedrius Kiršys

5,154
2
20
28

thank you very much @Giedrius Kiršys, I have end up with the same query in order to filter out the flagged telephones (i.e. `select * from mydb1.employees where exists (select * from mydb2.telephones where telephones.employee_id = employee.id and flag = 1`)). However I also need to have the flagged telephones eager loaded. – ira Apr 13 '16 at 07:19

Querying Relationship Existence using multiple MySQL database connections in Laravel 5.2

3 Answers3

Linked