在Laravel 5.2中使用多个MySQL数据库连接查询关系存在 [英] Querying Relationship Existence using multiple MySQL database connections in Laravel 5.2

问题描述

我正在处理以下情况:我有两个模型，一个具有id和name字段的Employee和一个具有id，employee_id和flag字段的Telephone.这两个模型之间也存在一对多关系，即一个雇员可能有很多电话，而一个电话可能属于一个雇员.

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);
    }
}

Employee模型引用存在于名为mydb1的数据库模式中的表employees，而Telephone模型与存在于名为mydb2的另一个数据库模式中的telephones表相关.

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.

我想要的是使用Eloquent，仅获取拥有至少一个电话且带有特定标志急切加载的员工.并且(如果可能)不是查询生成器

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)使用Controller中的whereHas方法

$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();

我在这里要做的是首先仅检索具有flag = 1的电话的员工，其次只希望加载这些电话，但是由于存在不同，我得到以下查询异常使用的数据库连接:

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:

找不到基本表或视图:表mydb1.telephones不存在(这是真的，电话存在于mydb2中)

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

2)控制器中受约束的热切

$employees = Employee::with([

    'telephones' => function ($query) {

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

])->get();

此方法渴望加载带有flag = 1的电话，但它会返回 all 个员工实例，这不是我真正想要的.我想收集仅具有flag = 1且具有电话的员工模型，不包括具有telephones = []

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 = []

推荐答案

考虑到

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:

1. 创建一个在模型中返回 Relation对象的方法
2. 急于加载控制器中的这一新关系
3. 使用模型中的查询范围过滤掉标志！= 1的电话

1. create a method that returns a Relation object in the Model
2. eager load this new relationship in the Controller
3. filtered out the telephones of flag != 1 using a query scope in the Model

在 Employee 模型中

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');
    });
}

在控制器中

现在我可以使用'la Eloquent'这样的优雅语法

Now I may use this elegant syntax a’la Eloquent

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

内容类似于获取所有急切加载了标志电话的员工，然后仅取走至少具有一部标志电话的员工"

处理了一段时间的Laravel框架(当前版本使用5.2.39)后，我发现实际上whereHas()子句在使用from()的关系数据库存在于不同数据库中的情况下确实起作用方法，如下所示:

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指出了使用from()方法的功劳，但是看来该方法需要同时使用数据库和表作为参数.

@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.

这篇关于在Laravel 5.2中使用多个MySQL数据库连接查询关系存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！