来自Laravel Query Builder中不同数据库的联合查询(Union queries from different databases in Laravel Query Builder)

我在两个不同的数据库中有两个相似的表。两个表都有一个带有日期的列和一个带有电子邮件地址的列。虽然列名不一样。结果我希望有一个结果包含来自两个表的所有记录。

所以我的第一步是：

$emails_1 = DB::connection('db1')->table('contacts_1')->select('mail_address AS email', 'date as created_at'); $emails_2 = DB::connection('db2')->table('contacts_2')->select('email', 'created_at');

所以现在我有两个结果，结果中的列名相同（email和created_at）。

现在我想将结果合并在一起，所以我这样做：

$all_emails = $emails_1->union($emails_2);

这是我得到错误的地方：

未找到基表或视图：1146表'db1.contacts_2'不存在（SQL :(选择mail_address作为email ， date为来自contacts_1 created_at ）union（选择email ，来自contacts_2 created_at ））

因此，查询构建器似乎与diferente表混淆。

有人帮忙吗？

I have two similar tables in two different databases. Both tables have a column with a date and one with email addresses. Though the column names are not the same. As result I want to have one result that contains all records from both tables.

So my first step is:

$emails_1 = DB::connection('db1')->table('contacts_1')->select('mail_address AS email', 'date as created_at'); $emails_2 = DB::connection('db2')->table('contacts_2')->select('email', 'created_at');

So now I have two results and the column names in the result are equal (email and created_at).

Now I want to merge the results together, so I do:

$all_emails = $emails_1->union($emails_2);

And this is where I get the error:

Base table or view not found: 1146 Table 'db1.contacts_2' doesn't exist (SQL: (select mail_address as email, date as created_at from contacts_1) union (select email, created_at from contacts_2))

So it seems that query builder gets confused with the diferente tables.

Has anyone help?

最满意答案

您不能使用不同的连接，但您仍然可以明确地提供db名称：

$q1 = DB::table('db1.contacts') // where(..) or anything you need here ->select('mail_address as email', 'date as created_at'); $q2 = DB::table('db2.contacts') // like above ->select('email', 'created_at'); $result = $q2->union($q1)->get();

You can't use different connections, but you still can do it providing the db name explicitly:

更多推荐