写一个“特殊”的查询构造器 - (六、关联)

摘要：虽然现在这样的情况已经很少，但是对于查询构造器而言，还是要提供一个方便的方法来对表前缀进行设置，特别是当你没有权限修改表名的时候。所以我们将表前缀作为一个配置参数传入查询构造器，在查询构造器的底层进行自动前缀添加。

关联查询是关系型数据库典型的查询语句，根据两个或多个表中的列之间的关系，从这些表中查询数据。在 SQL 标准中使用 JOIN 和 ON 关键字来实现关联查询。

join 子句的构造并不难，注意事项就是关联查询的注意事项：

写对语法和关联的条件

使用 table.field 模式防止字段重名

基类中新建 join() 方法：

// $table 要关联的表
// $one   作为关联条件的一个表的字段
// $two   作为关联条件的另一个表的字段
// $type  关联模式 inner、left、right
public function join($table, $one, $two, $type = "INNER")
{
    // 判断模式是否合法
    if( ! in_array($type, ["INNER", "LEFT", "RIGHT"])) {
        throw new InvalidArgumentException("Error join mode");
    }
    // 构建 join 子句字符串
    $this->_join_str .= " ".$type." JOIN ".self::_wrapRow($table).
        " ON ".self::_wrapRow($one)." = ".self::_wrapRow($two);
    return $this;
}

leftJoin() 和 rightJoin() 方法：

public function leftJoin($table, $one, $two)
{
    return $this->join($table, $one, $two, "LEFT");
}

public function rightJoin($table, $one, $two)
{
    return $this->join($table, $one, $two, "RIGHT");
}

注：Sqlite 是不支持 right join 的，所以 rightJoin() 方法在 Sqlite 驱动类中无效。

构建 SELECT student.name, class.name FROM student INNER JOIN class ON student.class_id = class.id;：

$results = $driver->table("student")
            ->select("student.name", "class.name")
            ->join("class", "student.class_id", "class.id")
            ->get();

以前很多数据表放在一个数据库中的时候，需要表前缀来区分功能。虽然现在这样的情况已经很少，但是对于查询构造器而言，还是要提供一个方便的方法来对表前缀进行设置，特别是当你没有权限修改表名的时候。

对于有表前缀的表，我们并不想每次都写一个前缀，这样会导致前缀更改后，应用层要跟着修改。所以我们将表前缀作为一个配置参数传入查询构造器，在查询构造器的底层进行自动前缀添加。

表前缀的配置，假设表前缀为 "test_" ：

// 以 mysql 为例
$config = [
    "host"        => "localhost",
    "port"        => "3306",
    "user"        => "username",
    "password"    => "password",
    "dbname"      => "dbname",
    "charset"     => "utf8",
    "prefix"      => "test_",
    "timezone"    => "+8:00",
    "collection"  => "utf8_general_ci",
    "strict"      => false,
    // "unix_socket" => "/var/run/mysqld/mysqld.sock",
];
$db = new Mysql($config);

进行自动添加前缀的方法：

protected function _wrapTable($table)
{
    // 构造函数传入的配置中有前缀参数吗？
    $prefix = array_key_exists("prefix", $this->_config) ?
            $this->_config["prefix"] : "";
    // 拼接前缀
    return $prefix.$table;
}

修改 table() 方法：

public function table($table)
{
    // 自动添加前缀
    $this->_table = self::_wrapRow($this->_wrapTable($table));

    return $this;
}

join 子句中也涉及到表，所以修改 join() 方法：

public function join($table, $one, $two, $type = "INNER")
{
    if( ! in_array($type, ["INNER", "LEFT", "RIGHT"])) {
        throw new InvalidArgumentException("Error join mode");
    }
    // 添加表前缀
    $table = $this->_wrapTable($table);
    
    $this->_join_str .= " ".$type." JOIN ".self::_wrapRow($table).
        " ON ".self::_wrapRow($one)." = ".self::_wrapRow($two);
    return $this;
}

增加了表前缀后，我们会发现一个问题：

使用 table()、join() 方法传入的表可以自动的添加前缀，但是 table.field 格式中的表没法自动添加前缀，如上面的 join("class", "student.class_id", "class.id")，我们总不能每次都写成 join("class", "test_student.class_id", "test_class.id") 这种 (这样的话和全部手工添加前缀没什么两样)，必须找到一个自动添加前缀的办法。

观察 table.field 模式，它出现的位置不定，可能在列、任何一个子句中出现，所以在固定的位置去添加前缀是不大可能的。那么我们反过来想一下，如果在 SQL 已经构造完成但还未执行时，这时已经知道有哪些地方使用了这种格式，去一一替换即可。那么如何知道有哪些地方使用了这种格式？

使用正则

我们用正则表达式找到 table.field 的 table 部分，给 table 加上表前缀即可 (这里不考虑跨库查询时三个点的情况)。

基类新增 _wrapPrepareSql() 方法：

// 替换 table.field 为 prefixtable.field
protected function _wrapPrepareSql()
{
    $quote = static::$_quote_symbol;
    $prefix_pattern = "/".$quote."([a-zA-Z0-9_]+)".$quote."(.)".$quote."([a-zA-Z0-9_]+)".$quote."/";
    $prefix_replace = self::_quote($this->_wrapTable("$1"))."$2".self::_quote("$3");

    $this->_prepare_sql = preg_replace($prefix_pattern, $prefix_replace, $this->_prepare_sql);
}

修改 _execute() 方法：

protected function _execute()
{
    try {
        // table.field 模式添加表前缀
        $this->_wrapPrepareSql();
        $this->_pdoSt = $this->_pdo->prepare($this->_prepare_sql);
        $this->_bindParams();
        $this->_pdoSt->execute();
        $this->_reset();
    } catch (PDOException $e) {
        if($this->_isTimeout($e)) { 

            $this->_closeConnection();
            $this->_connect();
            
            try {
                // table.field 模式添加表前缀
                $this->_wrapPrepareSql();
                $this->_pdoSt = $this->_pdo->prepare($this->_prepare_sql);
                $this->_bindParams();
                $this->_pdoSt->execute();
                $this->_reset();
            } catch (PDOException $e) {
                throw $e;
            }
        } else {
            throw $e;
        }
    }

}

最后我们进行一个完整的测试：

require_once dirname(dirname(__FILE__)) . "/vendor/autoload.php";

use DriversMysql;

$config = [
    "host"        => "localhost",
    "port"        => "3306",
    "user"        => "username",
    "password"    => "password",
    "dbname"      => "database",
    "prefix"      => "test_",
    "charset"     => "utf8",
    "timezone"    => "+8:00",
    "collection"  => "utf8_general_ci",
    "strict"      => false,
];

$driver = new Mysql($config);

$results = $driver->table("student")
    ->select("student.name", "class.name")
    ->join("class", "student.class_id", "class.id")
    ->get();

var_dump($results);

试试看吧！

到目前位置，查询相关的 SQL 构造方法基本开发完毕，我们进行一些复杂的 SQL 构造吧。

注：这里只是以我的测试环境举例，大家可以按照自己的思路去建表

构造语句 SELECT * FROM t_user WHERE username = "Jackie aa" OR ( NOT EXISTS ( SELECT * FROM t_user WHERE username = "Jackie aa" ) AND (username = "Jackie Conroy" OR username = "Jammie Haag")) AND g_id IN ( SELECT id FROM t_user_group) ORDER BY id DESC LIMIT 1 OFFSET 0 ：

$results = $driver->table("user")
            ->where("username", "Jackie aa")
            ->orWhereBrackets(function($query) {
                $query->whereNotExists(function($query) {
                    $query->table("user")->where("username", "Jackie aa");
                })->WhereBrackets(function($query) {
                    $query->where("username", "Jackie Conroy")
                            ->orWhere("username", "Jammie Haag");
                });
            })
            ->whereInSub("g_id", function($query) {
                $query->table("user_group")->select("id");
            })
            ->orderBy("id", "DESC")
            ->limit(0, 1)
            ->get();

构造语句 SELECT t_user.username, t_user_group.groupname FROM t_user LEFT JOIN t_user_group ON t_user.g_id = t_user_group.id WHERE username = "Jackie aa" OR ( NOT EXISTS ( SELECT * FROM t_user WHERE username = "Jackie aa" ) AND username = "Jackie Conroy" )：

$results = $driver->table("user")
            ->select("user.username", "user_group.groupname")
            ->leftJoin("user_group", "user.g_id", "user_group.id")
            ->where("user.username", "Jackie aa")
            ->orWhereBrackets(function($query) {
                $query->whereNotExists(function($query) {
                    $query->table("user")->where("username", "Jackie aa");
                })->where("user.username", "Jackie Conroy");
            })
            ->get();

更多例子参考 WorkerF 单元测试 - PDODQL