摘要：，看下源碼返回很容易知道返回值是，然后將該值存儲在變量中，這時。看下的源碼去除掉字符后為返回從源碼中可知道返回值為，這時。

說明：本文主要學習下Query Builder編譯Fluent Api為SQL的細節(jié)和執(zhí)行SQL的過程。實際上，上一篇聊到了IlluminateDatabaseQueryBuilder這個非常重要的類，這個類含有三個主要的武器：MySqlConnection, MySqlGrammar, MySqlProcessor。MySqlConnection主要就是在執(zhí)行SQL時做連接MySql數(shù)據(jù)庫操作，MySqlProcessor主要就是用來對執(zhí)行SQL后的數(shù)據(jù)集做后置處理操作，這兩點已經(jīng)在之前上篇聊過，那MySqlGrammar就是SQL語法編譯器，用來編譯Fluent Api為SQL。最后使用MySqlConnection::select($sql, $bindings)執(zhí)行SQL。

開發(fā)環(huán)境：Laravel5.3 + PHP7

看下toSql()的源碼：

    public function toSql()
    {
        // $this->grammar = new MySqlGrammar
        return $this->grammar->compileSelect($this);
    }
    
    public function compileSelect(Builder $query)
    {
        $sql = parent::compileSelect($query);

        // 從上一篇文章知道，$unions屬性沒有存儲值，$wheres屬性是有值的
        if ($query->unions) {
            $sql = "(".$sql.") ".$this->compileUnions($query);
        }

        return $sql;
    }

這里首先會調(diào)用IlluminateDatabaseQueryGrammarsGrammar::compileSelect(Builder $query)，看下compileSelect(Builder $query)的源碼：

    public function compileSelect(Builder $query)
    {
        // $original = ["*"]
        $original = $query->columns;

        if (is_null($query->columns)) {
            $query->columns = ["*"];
        }

        $sql = trim($this->concatenate($this->compileComponents($query)));

        $query->columns = $original;

        // $sql = "select * from users where id = ?"
        return $sql;
    }
    
    protected $selectComponents = [
        "aggregate",
        "columns",
        "from",
        "joins",
        "wheres",
        "groups",
        "havings",
        "orders",
        "limit",
        "offset",
        "lock",
    ];
    
    protected function compileComponents(Builder $query)
    {
        $sql = [];

        foreach ($this->selectComponents as $component) {
            // 
            if (! is_null($query->$component)) {
                $method = "compile".ucfirst($component);

                // 1. compileColumns($builder, ["*"]) -> "select " . $this->columnize(["*"])
                // 2. compileFrom($builder, "users"); -> "from ".$this->wrapTable("users")
                // 3. compileWheres($builder, [ 0 => ["type" => "basic", "column" => "id", "operator" => "=", "value" => 1, "boolean" => "and"], ])

                // $sql = ["columns" => "select *", "from" => "from users", "wheres" => "where id = ?"]

                $sql[$component] = $this->$method($query, $query->$component);
            }
        }

        return $sql;
    }

從上文源碼中可知道，首先依次遍歷片段集合:aggregate,columns,from,joins,wheres,groups,havings,orders,limit,offset,lock，查看屬性有無存儲值。在上文中知道，在片段$columns,from,wheres存有值為["*"], "users", [["type" => "basic", "column" => "id", "operator" => "=", "value" => 1, "boolean" => "and"]]，然后通過拼接字符串調(diào)用方法compileColumns($builder, ["*"]), compileFrom($builder, "users"), compileWheres($builder, array)，依次看下這些方法的源碼：

    protected function compileColumns(Builder $query, $columns)
    {   
        if (! is_null($query->aggregate)) {
            return;
        }

        // $select = "select "
        $select = $query->distinct ? "select distinct " : "select ";

        return $select.$this->columnize($columns);
    }
    
    // Illuminate/Database/Grammar
    public function columnize(array $columns)
    {
        // 依次經(jīng)過wrap()函數(shù)封裝下
        return implode(", ", array_map([$this, "wrap"], $columns));
    }
    
    public function wrap($value, $prefixAlias = false)
    {
        if ($this->isExpression($value)) {
            return $this->getValue($value);
        }

        if (strpos(strtolower($value), " as ") !== false) {
            $segments = explode(" ", $value);

            if ($prefixAlias) {
                $segments[2] = $this->tablePrefix.$segments[2];
            }

            return $this->wrap($segments[0])." as ".$this->wrapValue($segments[2]);
        }

        $wrapped = [];

        $segments = explode(".", $value);

        // $segments = ["*"]
        foreach ($segments as $key => $segment) {
            if ($key == 0 && count($segments) > 1) {
                $wrapped[] = $this->wrapTable($segment);
            } else {
                // $wrapped = ["*"]
                $wrapped[] = $this->wrapValue($segment);
            }
        }

        return implode(".", $wrapped);
    }
    
    protected function wrapValue($value)
    {
        if ($value === "*") {
            return $value;
        }

        return """.str_replace(""", """", $value).""";
    }

通過源碼很容易知道compileColumns($builder, ["*"])返回值select "*"，然后將該值以key-value形式存儲在$sql變量中，這時$sql = ["columns" => "select "*""]。
OK，看下compileFrom($builder,"users")源碼：

    protected function compileFrom(Builder $query, $table)
    {
        return "from ".$this->wrapTable($table);
    }
    
    // Illuminate/Database/Grammar
    public function wrapTable($table)
    {
        if ($this->isExpression($table)) {
            return $this->getValue($table);
        }
        
        // 返回"users"
        return $this->wrap($this->tablePrefix.$table, true);
    }

很容易知道返回值是from "users"，然后將該值存儲在$sql變量中，這時$sql = ["columns" => "select "*"", "from" => "from "users""]。OK,看下compileWheres($builder, array)的源碼：

    protected function compileWheres(Builder $query)
    {
        $sql = [];

        if (is_null($query->wheres)) {
            return "";
        }

        foreach ($query->wheres as $where) {
            $method = "where{$where["type"]}"; // "whereBasic"

            // "and " . $this->whereBasic($builder, ["type" => "basic", "column" => "id", "operator" => "=", "value" => 1, "boolean" => "and"]
            // -> $sql = ["and id = ?", ];
            $sql[] = $where["boolean"]." ".$this->$method($query, $where);
        }
        
        if (count($sql) > 0) {
            $sql = implode(" ", $sql);

            // $conjunction = "where"
            $conjunction = $query instanceof JoinClause ? "on" : "where";

            // 去除掉"and"字符后為"where id = ?"
            return $conjunction." ".$this->removeLeadingBoolean($sql);
        }

        return "";
    }
    
    protected function whereBasic(Builder $query, $where)
    {
        // $value = "?"
        $value = $this->parameter($where["value"]);

        // 返回"id = ?"
        return $this->wrap($where["column"])." ".$where["operator"]." ".$value;
    }

從源碼中可知道返回值為where id = ?，這時$sql = ["columns" => "select "*"", "from" => "from "users"", "wheres" => "where id = ?"]。

OK, 最后通過concatenate()函數(shù)把$sql值拼接成字符串select "*" from "users" where id = ?：

    protected function concatenate($segments)
    {
        return implode(" ", array_filter($segments, function ($value) {
            return (string) $value !== "";
        }));
    }

也就是說，通過SQL語法編譯器MySqlGrammar把table("users")->where("id", "=", 1)編譯成了SQL語句select * from users where id = ?。

上文聊到Builder::runSelect()調(diào)用了三個方法：MySqlConnection::select(), Builder::toSql(), Builder::getBindings()，其中Builder::toSql()通過SQL語法編譯器已經(jīng)編譯得到了SQL語句，Builder::getBindings()獲取存儲在$bindings[ ]的值。最后看下MySqlConnection::select()是如何執(zhí)行SQL語句的：

    public function select($query, $bindings = [], $useReadPdo = true)
    {
        // Closure就是用來執(zhí)行SQL,并把$query = "select * from users where id =?", $bindings = 1作為參數(shù)傳遞進去
        return $this->run($query, $bindings, function (Connection $me, $query, $bindings) use ($useReadPdo) {
            if ($me->pretending()) {
                return [];
            }

            // $statement = PDO::prepare("select * from users where id =?")
            /** @var PDOStatement $statement */
            $statement = $this->getPdoForSelect($useReadPdo)->prepare($query);

            $me->bindValues($statement, $me->prepareBindings($bindings));

            //PDO三步走: SQL編譯prepare() => 值綁定bindValue() => SQL執(zhí)行execute()
            // PDO通過這種方式防止SQL注入
            $statement->execute();

            $fetchMode = $me->getFetchMode();
            $fetchArgument = $me->getFetchArgument();
            $fetchConstructorArgument = $me->getFetchConstructorArgument();

            if ($fetchMode === PDO::FETCH_CLASS && ! isset($fetchArgument)) {
                $fetchArgument = "StdClass";
                $fetchConstructorArgument = null;
            }

            // PDOStatement::fetchAll(PDO::FETCH_OBJ);
            return isset($fetchArgument)
                ? $statement->fetchAll($fetchMode, $fetchArgument, $fetchConstructorArgument)
                : $statement->fetchAll($fetchMode);
        });
    }
    
    protected function run($query, $bindings, Closure $callback)
    {
        $this->reconnectIfMissingConnection();

        $start = microtime(true);

        try {
            // 執(zhí)行閉包函數(shù)
            $result = $this->runQueryCallback($query, $bindings, $callback);
        } catch (QueryException $e) {
            if ($this->transactions >= 1) {
                throw $e;
            }

            $result = $this->tryAgainIfCausedByLostConnection(
                $e, $query, $bindings, $callback
            );
        }

        $time = $this->getElapsedTime($start);

        $this->logQuery($query, $bindings, $time);

        return $result;
    }
    
    protected function runQueryCallback($query, $bindings, Closure $callback)
    {
        try {
            // 執(zhí)行閉包函數(shù)
            $result = $callback($this, $query, $bindings);
        }catch (Exception $e) {
            throw new QueryException(
                $query, $this->prepareBindings($bindings), $e
            );
        }

        return $result;
    }

通過源碼知道主要是執(zhí)行閉包來實現(xiàn)連接數(shù)據(jù)庫和執(zhí)行SQL操作，其中$statement = $this->getPdoForSelect($useReadPdo)->prepare($query)這句代碼實現(xiàn)了數(shù)據(jù)庫的連接操作和SQL語句送入MySQL服務器進行語句編譯。上文中提前聊了通過數(shù)據(jù)庫連接器MySqlConnector::connect()連接數(shù)據(jù)庫，這里知道實際上連接數(shù)據(jù)庫是在這個時刻才觸發(fā)的，Laravel5.0版本好像還沒有這么寫：

    protected function getPdoForSelect($useReadPdo = true)
    {
        return $useReadPdo ? $this->getReadPdo() : $this->getPdo();
    }
    
    public function getPdo()
    {
        if ($this->pdo instanceof Closure) {
            // 連接數(shù)據(jù)庫，獲得PDO實例
            return $this->pdo = call_user_func($this->pdo);
        }

        return $this->pdo;
    }

通過源碼知道執(zhí)行SQL操作很簡單，就是常見的PDO操作：PDO三步走: SQL編譯PDO::prepare() => 值綁定PDOStatement::bindValue() => SQL執(zhí)行PDOStatement::execute()。所以這里可看出Query Builder是在PHP PDO的基礎上實現(xiàn)的一層封裝，使得用更加面向?qū)ο蟮腇luent API來操作數(shù)據(jù)庫，而不需要寫一行SQL語句。

OK, 總的來說，通過了解Query Builder的實現(xiàn)原理后，知道其并不復雜或神秘，只是一個對PDO更友好封裝的包裹，Query Builder有幾個重要的類或概念：連接類MySqlConnection及其為其服務的連接器MySqlConnector;Builder 類;SQL語法解析器MySqlGrammar;后置處理器MySqlProcessor。

OK, illuminate/database package不僅提供了Query Builder，還提供了Eloquent ORM。那Eloquent ORM又是什么，與Query Builder是什么關系呢？既然有了Query Builder，為何還提供了Eloquent ORM呢？
實際上，Eloquent ORM又是對Query Builder的封裝，這樣可以實現(xiàn)更多好用且Query Builder所沒有的功能，如Model Relationships;Accessor/Mutator;Scopes等等。以后再聊Eloquent ORM的實現(xiàn)原理吧。

總結：本文主要學習了Query Builder編譯SQL細節(jié)和執(zhí)行SQL邏輯。后續(xù)在分享下Eloquent ORM的實現(xiàn)原理，到時見。

RightCapital招聘Laravel DevOps