联接来自不同数据库的2个查询

Question

嗨，这是连接来自不同数据库的2个查询的正确方法吗？或者没有办法在一个1功能中加入2个数据库

Model.php

public function getUserGroup($userId = null) 
{
if($userId) {
$sql = "SELECT * FROM user_group WHERE user_id = ?";
$query = $this->db->query($sql, array($userId));
$result = $query->row_array();
$group_id = $result['group_id'];
$g_sql = "SELECT * FROM groups WHERE id = ?";
$g_query = $this->db->query($g_sql, array($group_id));
$store_id = $result['store_id'];
$s_sql = "SELECT * FROM stores WHERE id = ?";
$s_query = $this->db->query($s_query, array($store_id));
$s_result = $s_query->row_array();
$t_result= $g_query ->row_array(), $s_result->row_array();
return $t_result;
}
}

Answer 1

您可以在mysql中加入表，查询位于演示链接下方

CREATE TABLE user_group (user_id INT,group_id INT,store_id INT);
INSERT INTO user_group VALUES (1,1,1);
INSERT INTO user_group VALUES (1,1,2);
INSERT INTO user_group VALUES (2,1,1);
INSERT INTO user_group VALUES (2,1,2);
-- fetch 
SELECT * FROM user_group;
CREATE TABLE `groups` (group_id INT,group_name varchar(255));
INSERT INTO `groups` VALUES (1,'group-1');
INSERT INTO `groups` VALUES (2,'group-2');
SELECT * FROM `groups`;

CREATE TABLE stores (store_id INT,store_name varchar(100));
INSERT INTO stores VALUES (1,'store-1');
INSERT INTO stores VALUES (2,'store-2');
SELECT * FROM stores;

SELECT * FROM user_group ug 
JOIN `groups` g on g.group_id = ug.group_id
JOIN stores s on s.store_id = ug.store_id
WHERE ug.user_id = 1;

你可以使用下面这样的代码点火器(CI(查询和你的参考链接

$this->db->select('*');
$this->db->from('user_group');
$this->db->join('groups', 'groups.group_id = user_group.group_id');
$this->db->join('stores', 'stores.store_id = user_group.store_id');
$this->db->where('user_group.user_id', 1); // <--- "1" = that is your's $userId 
$query = $this->db->get();