寫一個“特殊”的查詢構(gòu)造器 - (六、關(guān)聯(lián))

rainyang 發(fā)布于2019-07-01 12:29 / 1089人閱讀

摘要：雖然現(xiàn)在這樣的情況已經(jīng)很少，但是對于查詢構(gòu)造器而言，還是要提供一個方便的方法來對表前綴進(jìn)行設(shè)置，特別是當(dāng)你沒有權(quán)限修改表名的時候。所以我們將表前綴作為一個配置參數(shù)傳入查詢構(gòu)造器，在查詢構(gòu)造器的底層進(jìn)行自動前綴添加。

關(guān)聯(lián)查詢是關(guān)系型數(shù)據(jù)庫典型的查詢語句，根據(jù)兩個或多個表中的列之間的關(guān)系，從這些表中查詢數(shù)據(jù)。在 SQL 標(biāo)準(zhǔn)中使用 JOIN 和 ON 關(guān)鍵字來實現(xiàn)關(guān)聯(lián)查詢。

Join 子句

join 子句的構(gòu)造并不難，注意事項就是關(guān)聯(lián)查詢的注意事項：

寫對語法和關(guān)聯(lián)的條件

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

基類中新建 join() 方法：

// $table 要關(guān)聯(lián)的表
// $one   作為關(guān)聯(lián)條件的一個表的字段
// $two   作為關(guān)聯(lián)條件的另一個表的字段
// $type  關(guān)聯(lián)模式 inner、left、right
public function join($table, $one, $two, $type = "INNER")
{
    // 判斷模式是否合法
    if( ! in_array($type, ["INNER", "LEFT", "RIGHT"])) {
        throw new InvalidArgumentException("Error join mode");
    }
    // 構(gòu)建 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 驅(qū)動類中無效。

構(gòu)建 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();

表前綴 為什么要有表前綴

以前很多數(shù)據(jù)表放在一個數(shù)據(jù)庫中的時候，需要表前綴來區(qū)分功能。雖然現(xiàn)在這樣的情況已經(jīng)很少，但是對于查詢構(gòu)造器而言，還是要提供一個方便的方法來對表前綴進(jìn)行設(shè)置，特別是當(dāng)你沒有權(quán)限修改表名的時候。

自動添加表前綴的方法

對于有表前綴的表，我們并不想每次都寫一個前綴，這樣會導(dǎo)致前綴更改后，應(yīng)用層要跟著修改。所以我們將表前綴作為一個配置參數(shù)傳入查詢構(gòu)造器，在查詢構(gòu)造器的底層進(jìn)行自動前綴添加。

表前綴的配置，假設(shè)表前綴為 "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);

進(jìn)行自動添加前綴的方法：

protected function _wrapTable($table)
{
    // 構(gòu)造函數(shù)傳入的配置中有前綴參數(shù)嗎？
    $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.field 模式的表前綴添加

增加了表前綴后，我們會發(fā)現(xiàn)一個問題：

使用 table()、join() 方法傳入的表可以自動的添加前綴，但是 table.field 格式中的表沒法自動添加前綴，如上面的 join("class", "student.class_id", "class.id")，我們總不能每次都寫成 join("class", "test_student.class_id", "test_class.id") 這種 (這樣的話和全部手工添加前綴沒什么兩樣)，必須找到一個自動添加前綴的辦法。

觀察 table.field 模式，它出現(xiàn)的位置不定，可能在列、任何一個子句中出現(xiàn)，所以在固定的位置去添加前綴是不大可能的。那么我們反過來想一下，如果在 SQL 已經(jīng)構(gòu)造完成但還未執(zhí)行時，這時已經(jīng)知道有哪些地方使用了這種格式，去一一替換即可。那么如何知道有哪些地方使用了這種格式？

使用正則

我們用正則表達(dá)式找到 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;
        }
    }

}

最后我們進(jìn)行一個完整的測試：

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

試試看吧！

復(fù)雜語句的構(gòu)造

到目前位置，查詢相關(guān)的 SQL 構(gòu)造方法基本開發(fā)完畢，我們進(jìn)行一些復(fù)雜的 SQL 構(gòu)造吧。

注：這里只是以我的測試環(huán)境舉例，大家可以按照自己的思路去建表

構(gòu)造語句 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();

構(gòu)造語句 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