<article><h1>Laravel 數據庫之:數據庫請求構建器</h1><ul><li><a href="#introduction">簡介</a></li><li><a href="#retrieving-results">獲取結果</a><ul><li><a href="#chunking-results">分塊結果</a></li><li><a href="#aggregates">聚合</a></li></ul></li><li><a href="#selects">Selects</a></li><li><a href="#raw-expressions">原始表達式</a></li><li><a href="#joins">Joins</a></li><li><a href="#unions">Unions</a></li><li><a href="#where-clauses">Where 子句</a><ul><li><a href="#parameter-grouping">參數分組</a></li><li><a href="#where-exists-clauses">Where Exists 語法</a></li><li><a href="#json-where-clauses">JSON 查詢語句</a></li></ul></li><li><a href="#ordering-grouping-limit-and-offset">Ordering, Grouping, Limit, & Offset</a></li><li><a href="#conditional-clauses">條件語句</a></li><li><a href="#inserts">Inserts</a></li><li><a href="#updates">Updates</a><ul><li><a href="#updating-json-columns">更新 JSON</a></li><li><a href="#increment-and-decrement">自增或自減</a></li></ul></li><li><a href="#deletes">Deletes</a></li><li><a href="#pessimistic-locking">悲觀鎖</a></li></ul><p><a name="introduction"></a></p><h2><a href="#introduction">簡介</a></h2><p>Laravel 的數據庫查詢構造器提供了一個方便、流暢的接口,用來創建及運行數據庫查詢語句。它能用來執行應用程序中的大部分數據庫操作,且能在所有被支持的數據庫系統中使用。</p><p>Laravel 的查詢構造器使用 PDO 參數綁定,來保護你的應用程序免受 SQL 注入的攻擊。在綁定傳入字符串前不需要清理它們。</p><p><a name="retrieving-results"></a></p><h2><a href="#retrieving-results">獲取結果</a></h2><h4>從數據表中獲取所有的數據列</h4><p>你可以使用 <code class=" language-php"><span class="token constant">DB</span></code> facade 的 <code class=" language-php">table</code> 方法開始查詢。這個 <code class=" language-php">table</code> 方法針對查詢表返回一個查詢構造器實例,允許你在查詢時鏈式調用更多約束,并使用 <code class=" language-php">get</code> 方法獲取最終結果:</p><pre class=" language-php"><code class=" language-php"><span class="token delimiter"><?php</span>
<span class="token keyword">namespace</span> <span class="token package">App<span class="token punctuation">\</span>Http<span class="token punctuation">\</span>Controllers</span><span class="token punctuation">;</span>
<span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation">\</span>Support<span class="token punctuation">\</span>Facades<span class="token punctuation">\</span>DB</span><span class="token punctuation">;</span>
<span class="token keyword">use</span> <span class="token package">App<span class="token punctuation">\</span>Http<span class="token punctuation">\</span>Controllers<span class="token punctuation">\</span>Controller</span><span class="token punctuation">;</span>
<span class="token keyword">class</span> <span class="token class-name">UserController</span> <span class="token keyword">extends</span> <span class="token class-name">Controller</span>
<span class="token punctuation">{</span>
<span class="token comment" spellcheck="true">/**
* Show a list of all of the application's users.
*
* @return Response
*/</span>
<span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function">index<span class="token punctuation">(</span></span><span class="token punctuation">)</span>
<span class="token punctuation">{</span>
<span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">return</span> <span class="token function">view<span class="token punctuation">(</span></span><span class="token string">'user.index'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string">'users'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token variable">$users</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
<span class="token punctuation">}</span></code></pre><p><code class=" language-php">get</code> 方法會返回一個 <code class=" language-php">Illuminate\<span class="token package">Support<span class="token punctuation">\</span>Collection</span></code> 結果,其中每個結果都是一個 PHP <code class=" language-php">StdClass</code> 對象的實例。您可以通過訪問列中對象的屬性訪問每個列的值:</p><pre class=" language-php"><code class=" language-php"><span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$users</span> <span class="token keyword">as</span> <span class="token variable">$user</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token keyword">echo</span> <span class="token variable">$user</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">name</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span></code></pre><h4>從數據表中獲取單個列或行</h4><p>如果你只需要從數據表中獲取一行數據,則可以使用 <code class=" language-php">first</code> 方法。這個方法將返回單個 <code class=" language-php">StdClass</code> 對象:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$user</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'name'</span><span class="token punctuation">,</span> <span class="token string">'John'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">first<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">echo</span> <span class="token variable">$user</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">name</span><span class="token punctuation">;</span></code></pre><p>如果你不需要一整行數據,則可以使用 <code class=" language-php">value</code> 方法來從單條記錄中取出單個值。此方法將直接返回字段的值:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$email</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'name'</span><span class="token punctuation">,</span> <span class="token string">'John'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">value<span class="token punctuation">(</span></span><span class="token string">'email'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><h4>獲取一列的值</h4><p>如果你想要獲取一個包含單個字段值的集合,可以使用 <code class=" language-php">pluck</code> 方法。在下面的例子中,我們將取出 roles 表中 title 字段的集合:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$titles</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'roles'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">pluck<span class="token punctuation">(</span></span><span class="token string">'title'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$titles</span> <span class="token keyword">as</span> <span class="token variable">$title</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token keyword">echo</span> <span class="token variable">$title</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span></code></pre><p>你也可以在返回的數組中指定自定義的鍵值字段:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$roles</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'roles'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">pluck<span class="token punctuation">(</span></span><span class="token string">'title'</span><span class="token punctuation">,</span> <span class="token string">'name'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$roles</span> <span class="token keyword">as</span> <span class="token variable">$name</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token variable">$title</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token keyword">echo</span> <span class="token variable">$title</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span></code></pre><p><a name="chunking-results"></a></p><h3>結果分塊</h3><p>如果你需要操作數千條數據庫記錄,可以考慮使用 <code class=" language-php">chunk</code> 方法。這個方法每次只取出一小塊結果,并會將每個塊傳遞給一個 <code class=" language-php">閉包</code> 處理。這個方法對于編寫數千條記錄的 <a href="/docs/5.4/artisan">Artisan 命令</a> 是非常有用的。例如,讓我們把 <code class=" language-php">users</code> 表進行分塊,每次操作 100 條數據:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">orderBy<span class="token punctuation">(</span></span><span class="token string">'id'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">chunk<span class="token punctuation">(</span></span><span class="token number">100</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token variable">$users</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$users</span> <span class="token keyword">as</span> <span class="token variable">$user</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token comment" spellcheck="true"> //
</span> <span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>你可以從 <code class=" language-php">閉包</code> 中返回 <code class=" language-php"><span class="token boolean">false</span></code>,以停止對后續分塊的處理:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">orderBy<span class="token punctuation">(</span></span><span class="token string">'id'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">chunk<span class="token punctuation">(</span></span><span class="token number">100</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token variable">$users</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token comment" spellcheck="true"> // Process the records...
</span>
<span class="token keyword">return</span> <span class="token boolean">false</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><a name="aggregates"></a></p><h3>聚合</h3><p>查詢構造器也支持各種聚合方法,如 <code class=" language-php">count</code>、 <code class=" language-php">max</code>、 <code class=" language-php">min</code>、 <code class=" language-php">avg</code> 和 <code class=" language-php">sum</code>。你可以在創建查詢后調用其中的任意一個方法:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">count<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$price</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'orders'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">max<span class="token punctuation">(</span></span><span class="token string">'price'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>當然,你也可以將這些方法結合其它子句來進行查詢:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$price</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'orders'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'finalized'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">avg<span class="token punctuation">(</span></span><span class="token string">'price'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><a name="selects"></a></p><h2><a href="#selects">Selects</a></h2><h4>指定一個 Select 子句</h4><p>當然,你并不會總是想從數據表中選出所有的字段。這時可使用 <code class=" language-php">select</code> 方法自定義一個 <code class=" language-php">select</code> 子句來查詢指定的字段:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">select<span class="token punctuation">(</span></span><span class="token string">'name'</span><span class="token punctuation">,</span> <span class="token string">'email as user_email'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><code class=" language-php">distinct</code> 方法允許你強制讓查詢返回不重復的結果:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">distinct<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>如果你已有一個查詢構造器實例,并且希望在現有的 select 子句中加入一個字段,則可以使用 <code class=" language-php">addSelect</code> 方法:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$query</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">select<span class="token punctuation">(</span></span><span class="token string">'name'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$users</span> <span class="token operator">=</span> <span class="token variable">$query</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">addSelect<span class="token punctuation">(</span></span><span class="token string">'age'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><a name="raw-expressions"></a></p><h2><a href="#raw-expressions">原始表達式</a></h2><p>有時候你可能需要在查詢中使用原始表達式。這些表達式將會被當作字符串注入到查詢中,所以要小心避免造成 SQL 注入攻擊!要創建一個原始表達式,可以使用 <code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span>raw</code> 方法:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">select<span class="token punctuation">(</span></span><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">raw<span class="token punctuation">(</span></span><span class="token string">'count(*) as user_count, status'</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'status'</span><span class="token punctuation">,</span> <span class="token string">'<>'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">groupBy<span class="token punctuation">(</span></span><span class="token string">'status'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><a name="joins"></a></p><h2><a href="#joins">Joins</a></h2><h4>Inner Join 語法</h4><p>查詢構造器也可以編寫 join 語法。若要執行基本的「inner join」,你可以在查詢構造器實例上使用 <code class=" language-php">join</code> 方法。傳遞給 <code class=" language-php">join</code> 方法的第一個參數是你要 join 數據表的名稱,而其它參數則指定用來連接的字段約束。當然,如你所見,你可以在單個查找中連接多個數據表:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">join<span class="token punctuation">(</span></span><span class="token string">'contacts'</span><span class="token punctuation">,</span> <span class="token string">'users.id'</span><span class="token punctuation">,</span> <span class="token string">'='</span><span class="token punctuation">,</span> <span class="token string">'contacts.user_id'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">join<span class="token punctuation">(</span></span><span class="token string">'orders'</span><span class="token punctuation">,</span> <span class="token string">'users.id'</span><span class="token punctuation">,</span> <span class="token string">'='</span><span class="token punctuation">,</span> <span class="token string">'orders.user_id'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">select<span class="token punctuation">(</span></span><span class="token string">'users.*'</span><span class="token punctuation">,</span> <span class="token string">'contacts.phone'</span><span class="token punctuation">,</span> <span class="token string">'orders.price'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><h4>Left Join 語法</h4><p>如果你想用「left join」來代替「inner join」,請使用 <code class=" language-php">leftJoin</code> 方法。<code class=" language-php">leftJoin</code> 方法與 <code class=" language-php">join</code> 方法有著相同的用法:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">leftJoin<span class="token punctuation">(</span></span><span class="token string">'posts'</span><span class="token punctuation">,</span> <span class="token string">'users.id'</span><span class="token punctuation">,</span> <span class="token string">'='</span><span class="token punctuation">,</span> <span class="token string">'posts.user_id'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><h4>Cross Join 語法</h4><p>使用 <code class=" language-php">crossJoin</code> 方法和你想要交叉連接的表名來做「交叉連接」。交叉連接通過第一個表和連接表生成一個笛卡爾積:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'sizes'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">crossJoin<span class="token punctuation">(</span></span><span class="token string">'colours'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><h4>高級 Join 語法</h4><p>你還可以指定更高級的 join 子句。讓我們傳遞一個<code class=" language-php">閉包</code>作為 <code class=" language-php">join</code> 方法的第二個參數來作為開始。此<code class=" language-php">閉包</code>將會收到一個 <code class=" language-php">JoinClause</code> 對象,讓你可以在 <code class=" language-php">join</code> 子句中指定約束:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">join<span class="token punctuation">(</span></span><span class="token string">'contacts'</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token variable">$join</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token variable">$join</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">on<span class="token punctuation">(</span></span><span class="token string">'users.id'</span><span class="token punctuation">,</span> <span class="token string">'='</span><span class="token punctuation">,</span> <span class="token string">'contacts.user_id'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">orOn<span class="token punctuation">(</span></span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>如果你想要在連接中使用「where」風格的子句,則可以在連接中使用 <code class=" language-php">where</code> 和 <code class=" language-php">orWhere</code> 方法。這些方法將會比較值和對應的字段,而不是比較兩個字段:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">join<span class="token punctuation">(</span></span><span class="token string">'contacts'</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token variable">$join</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token variable">$join</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">on<span class="token punctuation">(</span></span><span class="token string">'users.id'</span><span class="token punctuation">,</span> <span class="token string">'='</span><span class="token punctuation">,</span> <span class="token string">'contacts.user_id'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'contacts.user_id'</span><span class="token punctuation">,</span> <span class="token string">'>'</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><a name="unions"></a></p><h2><a href="#unions">Unions</a></h2><p>查詢構造器也提供了一個快捷的方法來「合并」 兩個查詢。例如,你可以先創建一個初始查詢,并使用 <code class=" language-php">union</code> 方法將它與第二個查詢進行合并:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$first</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereNull<span class="token punctuation">(</span></span><span class="token string">'first_name'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereNull<span class="token punctuation">(</span></span><span class="token string">'last_name'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">union<span class="token punctuation">(</span></span><span class="token variable">$first</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><blockquote class="has-icon tip"><p><div class="flag"><span class="svg"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:a="http://ns.adobe.com/AdobeSVGViewerExtensions/3.0/" version="1.1" x="0px" y="0px" width="56.6px" height="87.5px" viewBox="0 0 56.6 87.5" enable-background="new 0 0 56.6 87.5" xml:space="preserve"><path fill="#FFFFFF" d="M28.7 64.5c-1.4 0-2.5-1.1-2.5-2.5v-5.7 -5V41c0-1.4 1.1-2.5 2.5-2.5s2.5 1.1 2.5 2.5v10.1 5 5.8C31.2 63.4 30.1 64.5 28.7 64.5zM26.4 0.1C11.9 1 0.3 13.1 0 27.7c-0.1 7.9 3 15.2 8.2 20.4 0.5 0.5 0.8 1 1 1.7l3.1 13.1c0.3 1.1 1.3 1.9 2.4 1.9 0.3 0 0.7-0.1 1.1-0.2 1.1-0.5 1.6-1.8 1.4-3l-2-8.4 -0.4-1.8c-0.7-2.9-2-5.7-4-8 -1-1.2-2-2.5-2.7-3.9C5.8 35.3 4.7 30.3 5.4 25 6.7 14.5 15.2 6.3 25.6 5.1c13.9-1.5 25.8 9.4 25.8 23 0 4.1-1.1 7.9-2.9 11.2 -0.8 1.4-1.7 2.7-2.7 3.9 -2 2.3-3.3 5-4 8L41.4 53l-2 8.4c-0.3 1.2 0.3 2.5 1.4 3 0.3 0.2 0.7 0.2 1.1 0.2 1.1 0 2.2-0.8 2.4-1.9l3.1-13.1c0.2-0.6 0.5-1.2 1-1.7 5-5.1 8.2-12.1 8.2-19.8C56.4 12 42.8-1 26.4 0.1zM43.7 69.6c0 0.5-0.1 0.9-0.3 1.3 -0.4 0.8-0.7 1.6-0.9 2.5 -0.7 3-2 8.6-2 8.6 -1.3 3.2-4.4 5.5-7.9 5.5h-4.1H28h-0.5 -3.6c-3.5 0-6.7-2.4-7.9-5.7l-0.1-0.4 -1.8-7.8c-0.4-1.1-0.8-2.1-1.2-3.1 -0.1-0.3-0.2-0.5-0.2-0.9 0.1-1.3 1.3-2.1 2.6-2.1H41C42.4 67.5 43.6 68.2 43.7 69.6zM37.7 72.5H26.9c-4.2 0-7.2 3.9-6.3 7.9 0.6 1.3 1.8 2.1 3.2 2.1h4.1 0.5 0.5 3.6c1.4 0 2.7-0.8 3.2-2.1L37.7 72.5z"></path></svg></span></div> 也可使用 <code class=" language-php">unionAll</code> 方法,它和 <code class=" language-php">union</code> 方法有著相同的用法。</p></blockquote><p><a name="where-clauses"></a></p><h2><a href="#where-clauses">Where 子句</a></h2><h4>簡單的 Where 子句</h4><p>你可以在查詢構造器實例中使用 <code class=" language-php">where</code> 方法從而把 <code class=" language-php">where</code> 子句加入到這個查詢中。基本的 <code class=" language-php">where</code> 方法需要3個參數。第一個參數是字段的名稱。第二個參數是運算符,它可以是數據庫所支持的任何運算符。最后,第三個參數是要對字段進行評估的值。</p><p>例如,這是一個要驗證「votes」字段的值等于 100 的查詢:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">,</span> <span class="token string">'='</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>為方便起見,如果你只是想簡單的校驗某個字段等于一個指定的值,你可以直接將這個值作為第二個參數傳入 <code class=" language-php">where</code> 方法:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>當然,在編寫 <code class=" language-php">where</code> 子句時,你也可以使用各種數據庫所支持其它的運算符:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">,</span> <span class="token string">'>='</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">,</span> <span class="token string">'<>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'name'</span><span class="token punctuation">,</span> <span class="token string">'like'</span><span class="token punctuation">,</span> <span class="token string">'T%'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>你也可以通過一個條件數組做 <code class=" language-php">where</code> 的查詢:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token punctuation">[</span>
<span class="token punctuation">[</span><span class="token string">'status'</span><span class="token punctuation">,</span> <span class="token string">'='</span><span class="token punctuation">,</span> <span class="token string">'1'</span><span class="token punctuation">]</span><span class="token punctuation">,</span>
<span class="token punctuation">[</span><span class="token string">'subscribed'</span><span class="token punctuation">,</span> <span class="token string">'<>'</span><span class="token punctuation">,</span> <span class="token string">'1'</span><span class="token punctuation">]</span><span class="token punctuation">,</span>
<span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><h4>Or 語法</h4><p>你可以在查詢中加入 <code class=" language-php"><span class="token keyword">or</span></code> 子句和 where 鏈式一起來約束查詢。<code class=" language-php">orWhere</code> 方法接收和 <code class=" language-php">where</code> 方法相同的參數:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">,</span> <span class="token string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">orWhere<span class="token punctuation">(</span></span><span class="token string">'name'</span><span class="token punctuation">,</span> <span class="token string">'John'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><h4>其它 Where 子句</h4><p><strong>whereBetween</strong></p><p><code class=" language-php">whereBetween</code> 方法用來驗證字段的值介于兩個值之間:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereBetween<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><strong>whereNotBetween</strong></p><p><code class=" language-php">whereNotBetween</code> 方法驗證字段的值 <strong>不</strong> 在兩個值之間:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereNotBetween<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">]</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><strong>whereIn 與 whereNotIn</strong></p><p><code class=" language-php">whereIn</code> 方法驗證字段的值包含在指定的數組內:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereIn<span class="token punctuation">(</span></span><span class="token string">'id'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">]</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><code class=" language-php">whereNotIn</code> 方法驗證字段的值 <strong>不</strong> 包含在指定的數組內:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereNotIn<span class="token punctuation">(</span></span><span class="token string">'id'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">]</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><strong>whereNull 與 whereNotNull</strong></p><p><code class=" language-php">whereNull</code> 方法驗證字段的值為 <code class=" language-php"><span class="token keyword">NULL</span></code>:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereNull<span class="token punctuation">(</span></span><span class="token string">'updated_at'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><code class=" language-php">whereNotNull</code> 方法驗證字段的值 <strong>不</strong> 為 <code class=" language-php"><span class="token keyword">NULL</span></code>:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereNotNull<span class="token punctuation">(</span></span><span class="token string">'updated_at'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><strong>whereDate / whereMonth / whereDay / whereYear</strong></p><p><code class=" language-php">whereDate</code> 方法比較某字段的值與指定的日期是否相等:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereDate<span class="token punctuation">(</span></span><span class="token string">'created_at'</span><span class="token punctuation">,</span> <span class="token string">'2016-12-31'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><code class=" language-php">whereMonth</code> 方法比較某字段的值是否與一年的某一個月份相等:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereMonth<span class="token punctuation">(</span></span><span class="token string">'created_at'</span><span class="token punctuation">,</span> <span class="token string">'12'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><code class=" language-php">whereDay</code> 方法比較某列的值是否與一月中的某一天相等:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereDay<span class="token punctuation">(</span></span><span class="token string">'created_at'</span><span class="token punctuation">,</span> <span class="token string">'31'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><code class=" language-php">whereYear</code> 方法比較某列的值是否與指定的年份相等:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereYear<span class="token punctuation">(</span></span><span class="token string">'created_at'</span><span class="token punctuation">,</span> <span class="token string">'2016'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><strong>whereColumn</strong></p><p><code class=" language-php">whereColumn</code> 方法用來檢測兩個列的數據是否一致:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereColumn<span class="token punctuation">(</span></span><span class="token string">'first_name'</span><span class="token punctuation">,</span> <span class="token string">'last_name'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>此方法還可以使用運算符:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereColumn<span class="token punctuation">(</span></span><span class="token string">'updated_at'</span><span class="token punctuation">,</span> <span class="token string">'>'</span><span class="token punctuation">,</span> <span class="token string">'created_at'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><code class=" language-php">whereColumn</code> 方法可以接收數組參數。條件語句會使用 <code class=" language-php"><span class="token keyword">and</span></code> 連接起來:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereColumn<span class="token punctuation">(</span></span><span class="token punctuation">[</span>
<span class="token punctuation">[</span><span class="token string">'first_name'</span><span class="token punctuation">,</span> <span class="token string">'='</span><span class="token punctuation">,</span> <span class="token string">'last_name'</span><span class="token punctuation">]</span><span class="token punctuation">,</span>
<span class="token punctuation">[</span><span class="token string">'updated_at'</span><span class="token punctuation">,</span> <span class="token string">'>'</span><span class="token punctuation">,</span> <span class="token string">'created_at'</span><span class="token punctuation">]</span>
<span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><a name="parameter-grouping"></a></p><h3>參數分組</h3><p>有時你可能需要創建更高級的 where 子句,例如「where exists」或者嵌套的參數分組。Laravel 的查詢構造器也能夠處理這些。讓我們先來看一個在括號中將約束分組的示例:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'name'</span><span class="token punctuation">,</span> <span class="token string">'='</span><span class="token punctuation">,</span> <span class="token string">'John'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">orWhere<span class="token punctuation">(</span></span><span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token variable">$query</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token variable">$query</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">,</span> <span class="token string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'title'</span><span class="token punctuation">,</span> <span class="token string">'<>'</span><span class="token punctuation">,</span> <span class="token string">'Admin'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>如你所見,上面例子會傳遞一個 <code class=" language-php">閉包</code> 到 <code class=" language-php">orWhere</code> 方法,告訴查詢構造器開始一個約束分組。此 <code class=" language-php">閉包</code> 接收一個查詢構造器實例,你可用它來設置應包含在括號分組內的約束。這個例子會生成以下 SQL:</p><pre class=" language-php"><code class=" language-php">select <span class="token operator">*</span> from users where name <span class="token operator">=</span> <span class="token string">'John'</span> <span class="token keyword">or</span> <span class="token punctuation">(</span>votes <span class="token operator">></span> <span class="token number">100</span> <span class="token keyword">and</span> title <span class="token operator"><</span><span class="token operator">></span> <span class="token string">'Admin'</span><span class="token punctuation">)</span></code></pre><p><a name="where-exists-clauses"></a></p><h3>Where Exists 語法</h3><p><code class=" language-php">whereExists</code> 方法允許你編寫 <code class=" language-php">where exists</code> SQL 子句。此方法會接收一個 <code class=" language-php">閉包</code> 參數,此閉包接收一個查詢語句構造器實例,讓你可以定義應放在「exists」SQL 子句中的查找:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereExists<span class="token punctuation">(</span></span><span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token variable">$query</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token variable">$query</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">select<span class="token punctuation">(</span></span><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">raw<span class="token punctuation">(</span></span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">from<span class="token punctuation">(</span></span><span class="token string">'orders'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">whereRaw<span class="token punctuation">(</span></span><span class="token string">'orders.user_id = users.id'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>上述查詢將生成以下 SQL:</p><pre class=" language-php"><code class=" language-php">select <span class="token operator">*</span> from users
where exists <span class="token punctuation">(</span>
select <span class="token number">1</span> from orders where orders<span class="token punctuation">.</span>user_id <span class="token operator">=</span> users<span class="token punctuation">.</span>id
<span class="token punctuation">)</span></code></pre><p><a name="json-where-clauses"></a></p><h3>JSON 查詢語句</h3><p>Laravel 也支持查詢 JSON 類型的字段。目前,本特性僅支持 MySQL 5.7+ 和 Postgres數據庫。可以使用 <code class=" language-php"><span class="token operator">-</span><span class="token operator">></span></code> 運算符來查詢 JSON 列數據:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'options->language'</span><span class="token punctuation">,</span> <span class="token string">'en'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'preferences->dining->meal'</span><span class="token punctuation">,</span> <span class="token string">'salad'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><a name="ordering-grouping-limit-and-offset"></a></p><h2><a href="#ordering-grouping-limit-and-offset">Ordering, Grouping, Limit 及 Offset</a></h2><h4>orderBy</h4><p><code class=" language-php">orderBy</code> 方法允許你根據指定字段對查詢結果進行排序。<code class=" language-php">orderBy</code> 方法的第一個參數是你想要用來排序的字段,而第二個參數則控制排序的順序,可以為 <code class=" language-php">asc</code> 或 <code class=" language-php">desc</code>:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">orderBy<span class="token punctuation">(</span></span><span class="token string">'name'</span><span class="token punctuation">,</span> <span class="token string">'desc'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><h4>latest / oldest</h4><p><code class=" language-php">latest</code> 和 <code class=" language-php">oldest</code> 方法允許你更容易的依據日期對查詢結果排序。默認查詢結果將依據 <code class=" language-php">created_at</code> 列。或者,你可以使用字段名稱排序:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$user</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">latest<span class="token punctuation">(</span></span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">first<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><h4>inRandomOrder</h4><p><code class=" language-php">inRandomOrder</code> 方法可以將查詢結果隨機排序。例如,你可以使用這個方法獲取一個隨機用戶:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$randomUser</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">inRandomOrder<span class="token punctuation">(</span></span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">first<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><h4>groupBy / having / havingRaw</h4><p><code class=" language-php">groupBy</code> 和 <code class=" language-php">having</code> 方法可用來對查詢結果進行分組。<code class=" language-php">having</code> 方法的用法和 <code class=" language-php">where</code> 方法類似:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">groupBy<span class="token punctuation">(</span></span><span class="token string">'account_id'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">having<span class="token punctuation">(</span></span><span class="token string">'account_id'</span><span class="token punctuation">,</span> <span class="token string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><code class=" language-php">havingRaw</code> 方法可以將一個原始的表達式設置為 <code class=" language-php">having</code> 子句的值。例如,我們能找出所有銷售額超過 2,500 元的部門:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'orders'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">select<span class="token punctuation">(</span></span><span class="token string">'department'</span><span class="token punctuation">,</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">raw<span class="token punctuation">(</span></span><span class="token string">'SUM(price) as total_sales'</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">groupBy<span class="token punctuation">(</span></span><span class="token string">'department'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">havingRaw<span class="token punctuation">(</span></span><span class="token string">'SUM(price) > 2500'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><h4>skip / take</h4><p>你可以使用 <code class=" language-php">skip</code> 和 <code class=" language-php">take</code> 方法來限制查詢結果數量或略過指定數量的查詢:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">skip<span class="token punctuation">(</span></span><span class="token number">10</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">take<span class="token punctuation">(</span></span><span class="token number">5</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>或者,你也可以使用 <code class=" language-php">limit</code> 和 <code class=" language-php">offset</code> 方法:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">offset<span class="token punctuation">(</span></span><span class="token number">10</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">limit<span class="token punctuation">(</span></span><span class="token number">5</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><a name="conditional-clauses"></a></p><h2><a href="#conditional-clauses">條件語句</a></h2><p>有時候,你希望某個值為 true 時才執行查詢。例如,如果在傳入請求中存在指定的輸入值的時候才執行這個 <code class=" language-php">where</code> 語句。你可以使用 <code class=" language-php">when</code> 方法實現:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$role</span> <span class="token operator">=</span> <span class="token variable">$request</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">input<span class="token punctuation">(</span></span><span class="token string">'role'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">when<span class="token punctuation">(</span></span><span class="token variable">$role</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token variable">$query</span><span class="token punctuation">)</span> <span class="token keyword">use</span> <span class="token punctuation">(</span><span class="token variable">$role</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token keyword">return</span> <span class="token variable">$query</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'role_id'</span><span class="token punctuation">,</span> <span class="token variable">$role</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>只有當 <code class=" language-php">when</code> 方法的第一個參數為 <code class=" language-php"><span class="token boolean">true</span></code> 時,閉包里的 <code class=" language-php">where</code> 語句才會執行。如果第一個參數是 <code class=" language-php"><span class="token boolean">false</span></code>,這個閉包將不會被執行。</p><p>你可能會把另一個閉包當作第三個參數傳遞給 <code class=" language-php">when</code> 方法。如果第一個參數的值為 <code class=" language-php"><span class="token boolean">false</span></code> 時,這個閉包將執行。為了說明如何使用此功能,我們將使用它配置默認排序的查詢:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$sortBy</span> <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
<span class="token variable">$users</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">when<span class="token punctuation">(</span></span><span class="token variable">$sortBy</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token variable">$query</span><span class="token punctuation">)</span> <span class="token keyword">use</span> <span class="token punctuation">(</span><span class="token variable">$sortBy</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token keyword">return</span> <span class="token variable">$query</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">orderBy<span class="token punctuation">(</span></span><span class="token variable">$sortBy</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">,</span> <span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token variable">$query</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token keyword">return</span> <span class="token variable">$query</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">orderBy<span class="token punctuation">(</span></span><span class="token string">'name'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><a name="inserts"></a></p><h2><a href="#inserts">Inserts</a></h2><p>查詢構造器也提供了 <code class=" language-php">insert</code> 方法,用來插入記錄到數據表中。<code class=" language-php">insert</code> 方法接收一個包含字段名和值的數組作為參數:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">insert<span class="token punctuation">(</span></span>
<span class="token punctuation">[</span><span class="token string">'email'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token string">'john@example.com'</span><span class="token punctuation">,</span> <span class="token string">'votes'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token number">0</span><span class="token punctuation">]</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>你甚至可以在 <code class=" language-php">insert</code> 調用中傳入一個嵌套數組向表中插入多條記錄。每個數組表示要插入表中的行:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">insert<span class="token punctuation">(</span></span><span class="token punctuation">[</span>
<span class="token punctuation">[</span><span class="token string">'email'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token string">'taylor@example.com'</span><span class="token punctuation">,</span> <span class="token string">'votes'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">,</span>
<span class="token punctuation">[</span><span class="token string">'email'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token string">'dayle@example.com'</span><span class="token punctuation">,</span> <span class="token string">'votes'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token number">0</span><span class="token punctuation">]</span>
<span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><h4>自增 ID</h4><p>若數據表存在自增 id,則可以使用 <code class=" language-php">insertGetId</code> 方法來插入記錄并獲取其 ID:</p><pre class=" language-php"><code class=" language-php"><span class="token variable">$id</span> <span class="token operator">=</span> <span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">insertGetId<span class="token punctuation">(</span></span>
<span class="token punctuation">[</span><span class="token string">'email'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token string">'john@example.com'</span><span class="token punctuation">,</span> <span class="token string">'votes'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token number">0</span><span class="token punctuation">]</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><blockquote class="has-icon note"><p><div class="flag"><span class="svg"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:a="http://ns.adobe.com/AdobeSVGViewerExtensions/3.0/" version="1.1" x="0px" y="0px" width="90px" height="90px" viewBox="0 0 90 90" enable-background="new 0 0 90 90" xml:space="preserve"><path fill="#FFFFFF" d="M45 0C20.1 0 0 20.1 0 45s20.1 45 45 45 45-20.1 45-45S69.9 0 45 0zM45 74.5c-3.6 0-6.5-2.9-6.5-6.5s2.9-6.5 6.5-6.5 6.5 2.9 6.5 6.5S48.6 74.5 45 74.5zM52.1 23.9l-2.5 29.6c0 2.5-2.1 4.6-4.6 4.6 -2.5 0-4.6-2.1-4.6-4.6l-2.5-29.6c-0.1-0.4-0.1-0.7-0.1-1.1 0-4 3.2-7.2 7.2-7.2 4 0 7.2 3.2 7.2 7.2C52.2 23.1 52.2 23.5 52.1 23.9z"></path></svg></span></div> 當使用 PostgreSQL 時,insertGetId 方法將預測自動遞增字段的名稱為 <code class=" language-php">id</code>。若你要從不同「順序」來獲取 ID,則可以將順序名稱作為第二個參數傳遞給 <code class=" language-php">insertGetId</code> 方法。</p></blockquote><p><a name="updates"></a></p><h2><a href="#updates">Updates</a></h2><p>當然,除了在數據庫中插入記錄外,你也可以使用 <code class=" language-php">update</code> 來更新已存在的記錄。<code class=" language-php">update</code> 方法和 <code class=" language-php">insert</code> 方法一樣,接收含有字段及值的數組,其中包括要更新的字段。可以使用 <code class=" language-php">where</code> 子句來約束 <code class=" language-php">update</code> 查找:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'id'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">update<span class="token punctuation">(</span></span><span class="token punctuation">[</span><span class="token string">'votes'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token number">1</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><a name="updating-json-columns"></a></p><h3>Updating JSON Columns</h3><p>當更新一個JSON 列時,你應該使用 <code class=" language-php"><span class="token operator">-</span><span class="token operator">></span></code> 語法來訪問 JSON 對象的鍵。僅在數據庫支持 JSON 列的時候才可使用這個操作:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'id'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span>
<span class="token operator">-</span><span class="token operator">></span><span class="token function">update<span class="token punctuation">(</span></span><span class="token punctuation">[</span><span class="token string">'options->enabled'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token boolean">true</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><a name="increment-and-decrement"></a></p><h3>自增或自減</h3><p>查詢構造器也為指定字段提供了便利的自增和自減方法 。此方法提供了一個比手動編寫 <code class=" language-php">update</code> 語法更具表達力且更精練的接口。</p><p>這兩個方法都必須接收至少一個參數(要修改的字段)。也可選擇傳入第二個參數,用來控制字段應遞增/遞減的量:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">increment<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">increment<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">decrement<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">decrement<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>您還可以指定要操作中更新其它字段:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">increment<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string">'name'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token string">'John'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><a name="deletes"></a></p><h2><a href="#deletes">Deletes</a></h2><p>查詢構造器也可使用 <code class=" language-php">delete</code> 方法從數據表中刪除記錄。在 <code class=" language-php">delete</code> 前,還可使用 <code class=" language-php">where</code> 子句來約束 <code class=" language-php">delete</code> 語法:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">delete<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">,</span> <span class="token string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">delete<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>如果你需要清空表,你可以使用 <code class=" language-php">truncate</code> 方法,這將刪除所有行,并重置自動遞增 ID 為零:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">truncate<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p><a name="pessimistic-locking"></a></p><h2><a href="#pessimistic-locking">悲觀鎖</a></h2><p>查詢構造器也包含一些可以幫助你在 <code class=" language-php">select</code> 語法上實現「悲觀鎖定」的函數 。若要在查詢中使用「共享鎖」,可以使用 <code class=" language-php">sharedLock</code> 方法。共享鎖可防止選中的數據列被篡改,直到事務被提交為止:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">,</span> <span class="token string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">sharedLock<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><p>另外,你也可以使用 <code class=" language-php">lockForUpdate</code> 方法。使用「更新」鎖可避免行被其它共享鎖修改或選取:</p><pre class=" language-php"><code class=" language-php"><span class="token scope">DB<span class="token punctuation">::</span></span><span class="token function">table<span class="token punctuation">(</span></span><span class="token string">'users'</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">where<span class="token punctuation">(</span></span><span class="token string">'votes'</span><span class="token punctuation">,</span> <span class="token string">'>'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">lockForUpdate<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">get<span class="token punctuation">(</span></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre><h2>譯者署名</h2><table><thead><tr><th>用戶名</th><th>頭像</th><th>職能</th><th>簽名</th></tr></thead><tbody><tr><td><a href="https://github.com/iwzh">@iwzh</a></td><td><img class="avatar-66 rm-style" src="https://dn-phphub.qbox.me/uploads/avatars/3762_1456807721.jpeg?imageView2/1/w/200/h/200"></td><td>翻譯</td><td>碼不能停 <a href="https://github.com/iwzh">@iwzh</a> at Github</td></tr></tbody></table></article>
- 入門指南
- 安裝
- 配置信息
- 文件夾結構
- 請求周期
- 開發環境部署
- Valet
- Homestead
- 核心概念
- 服務提供者
- Facades
- Contracts
- 服務容器
- HTTP 層
- 路由
- 中間件
- CSRF 保護
- 控制器
- 請求
- 響應
- 視圖
- Session
- 表單驗證
- 前端
- Blade 模板
- 本地化
- 前端指南
- 編輯資源 Mix
- 安全
- API 認證
- 用戶認證
- 用戶授權
- 加密解密
- 哈希
- 重置密碼
- 數據庫
- 快速入門
- 查詢構造器
- 分頁
- 數據庫遷移
- Redis
- 數據填充
- Eloquent ORM
- Eloquent ORM快速入門
- 模型關聯
- Eloquent 集合
- 修改器
- 序列化
- 綜合話題
- Artisan 命令行
- 廣播系統
- 緩存系統
- 集合
- 錯誤與日志
- 事件系統
- 文件存儲
- 輔助函數
- 郵件發送
- 消息通知
- 擴展包開發
- 隊列
- 任務調度