Yii2查詢where條件組裝詳解

<p>熟悉Yii2的查詢條件後，用Active Record查詢資料非常方便。 以下我們介紹where()方法當中，條件的拼裝方式。 </p> <h2 id="語法">1 語法</h2> <p style="white-space:pre-wrap;">Yii2用<code class="hljs bash" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-built_in">where</span>()</code>方法（當然還有其他方法）來實作條件篩選，語法：##<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>public $this where ( $condition, $params = [] )</pre><div class="contentsignin">登入後複製</div></div> </p><p style="white-space:pre-wrap;"><code class="hljs bash" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">$params<span class="hljs-variable" style="color:rgb(223,80,0);"></span>為可選參數，指定要綁定查詢的值。 </code></p><p style="white-space:pre-wrap;"><code class="hljs bash" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">$condition<span class="hljs-variable" style="color:rgb(223,80,0);"></span>為必選參數，</code><code class="hljs bash" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">$condition<span class="hljs-variable" style="color:rgb(223,80,0);"></span>可以是字串（如</code><code class="hljs bash" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">'id=1'<span class="hljs-string" style="color:rgb(223,80,0);"></span>）或陣列。 </code></p><p style="white-space:pre-wrap;"><code class="hljs bash" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">$condition<span class="hljs-variable" style="color:rgb(223,80,0);"></span>當陣列時，有兩種格式：</code></p><ul style="margin-top:0px;list-style-position:inside;" class=" list-paddingleft-2"><li>雜湊格式：<p>[' column1' => value1, 'column2' => value2, ...]<code class="hljs json" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;white-space:pre-wrap;"></code></p></li><li>#運算子格式：<p>[operator, operand1, operand2, ...]<code class="hljs json" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;white-space:pre-wrap;"></code></p></li></ul>2 哈希格式<h2 style="font-family:'Microsoft YaHei UI Light', SimHei, SimSun, sans-serif;line-height:1.1;color:rgb(66,139,209);"></h2>通常，哈希格式的查詢條件產生這樣的SQL語句：<p style="white-space:pre-wrap;"> <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>column1=value1 AND column2=value2 AND ...</pre><div class="contentsignin">登入後複製</div></div></p>如果某個值是數組，就會產生<p style="white-space:pre-wrap;">IN<code class="hljs" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">語句。 </code></p>如果某個值為<p style="white-space:pre-wrap;"><code class="hljs coffeescript" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">null<span class="hljs-literal" style="color:rgb(0,134,179);"></span>，會用</code>IS<code class="hljs cpp" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">NULL<span class="hljs-literal" style="color:rgb(0,134,179);"></span>來產生語句。 </code></p>範例：<p style="white-space:pre-wrap;"><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>[&#39;type&#39; => 1, &#39;status&#39; => 2] // 生成：(type = 1) AND (status = 2)[&#39;id&#39; => [1, 2, 3], &#39;status&#39; => 2] // 生成：(id IN (1, 2, 3)) AND (status = 2)[&#39;status&#39; => null] // 生成：status IS NULL</pre><div class="contentsignin">登入後複製</div></div></p>3 運算子格式<h2 style="font-family:'Microsoft YaHei UI Light', SimHei, SimSun, sans-serif;line-height:1.1;color:rgb(66,139,209);"></h2>在運算子格式，Yii會根據指定的運算子產生SQL語句。 <p style="white-space:pre-wrap;"><p style="white-space:pre-wrap;">運算子有：<code class="hljs coffeescript" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-keyword" style="color:rgb(167,29,93);">和</span></code>、<code class="hljs coffeescript" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-keyword" style="color:rgb(167,29,93);">或</span></code>、<code class="hljs coffeescript" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-keyword" style="color:rgb(167,29,93);">不是</span></code>、<code class="hljs" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">#之間</code>、<code class="hljs coffeescript" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-keyword" style="color:rgb(167,29,93);">不</span> 之間</code>、<code class="hljs bash" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-keyword" style="color:rgb(167,29,93);">中</span></code>、<code class="hljs coffeescript" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-keyword" style="color:rgb(167,29,93);">不</span><span class="hljs-keyword" style="color:rgb(167,29,93);"></span></code>、<code class="hljs" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">像</code>、## ＃＃＃＃或＃＃＃ 喜歡<code class="hljs coffeescript" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">#、<span class="hljs-keyword" style="color:rgb(167,29,93);"></span>不</code> 喜歡<code class="hljs coffeescript" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">#、<span class="hljs-keyword" style="color:rgb(167,29,93);"></span>或</code><code class="hljs coffeescript" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">不<span class="hljs-keyword" style="color:rgb(167,29,93);"># 喜歡</span>#、<span class="hljs-keyword" style="color:rgb(167,29,93);"></span>存在</code>##、<code class="hljs perl" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-keyword" style="color:rgb(167,29,93);">不</span></code>存在<code class="hljs perl" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"></code>、<code class="hljs" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">></code>、<code class="hljs" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><</code>、<code class="hljs" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">=</code>、<code class="hljs" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">>=</code>、<code class="hljs" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><=</code>、<code class="hljs diff" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-addition" style="color:rgb(85,165,50);background-color:rgb(234,255,234);">!=</span></code>等。</p><h3 id="对比">3.1 对比</h3><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>[&#39;>&#39;, &#39;id&#39;, 1] // 生成：id > 1[&#39;<&#39;, &#39;id&#39;, 100] // 生成：id < 100[&#39;=&#39;, &#39;id&#39;, 10] // 生成：id = 10[&#39;>=&#39;, &#39;id&#39;, 1] // 生成：id >= 1[&#39;<=&#39;, &#39;id&#39;, 100] // 生成：id != 10</pre><div class="contentsignin">登入後複製</div></div><p style="white-space:pre-wrap;">具体生成的SQL语句，运算符<code class="hljs objectivec" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-keyword" style="color:rgb(167,29,93);">id</span></code>会自动加上反斜杠引号<code class="hljs" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">`</code>，运算数会自动转义。</p><h3 id="and">3.2 and</h3><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>[&#39;and&#39;, &#39;id&#39; => 1, &#39;id&#39; => 2] // 生成：id=1 AND id=2[&#39;and&#39;, &#39;id=1&#39;, &#39;id=2&#39;] // 生成：id=1 AND id=2[&#39;and&#39;, &#39;type=1&#39;, [&#39;or&#39;, &#39;id=1&#39;, &#39;id=2&#39;]] // 生成：type=1 AND (id=1 OR id=2)</pre><div class="contentsignin">登入後複製</div></div><p style="white-space:pre-wrap;">在第2条和第3条语句中，列名称和搜索值未用键值关系指定，所以生成的SQL不会添加引号，也不会转义。</p><h3 id="or">3.3 or</h3><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>[&#39;or&#39;, [&#39;type&#39; => [7, 8, 9]], [&#39;id&#39; => [1, 2, 3]]] // 生成：(type IN (7, 8, 9) OR (id IN (1, 2, 3)))</pre><div class="contentsignin">登入後複製</div></div><h3 id="not">3.4 not</h3><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>[&#39;not&#39;, [&#39;attribute&#39; => null]] // 生成：NOT (attribute IS NULL)</pre><div class="contentsignin">登入後複製</div></div><h3 id="between和not-between">3.5 between和not between</h3><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>[&#39;between&#39;, &#39;id&#39;, 1, 10] // 生成：id BETWEEN 1 AND 10[&#39;not between&#39;, &#39;id&#39;, 1, 10] // 生成：id NOT BETWEEN 1 AND 10</pre><div class="contentsignin">登入後複製</div></div><p style="white-space:pre-wrap;">运算符后面的运算数1为数据表<span style="font-weight:bolder;">列名称</span>，运算数2和运算数3分别为列值范围的<span style="font-weight:bolder;">最小值</span>和<span style="font-weight:bolder;">最大值</span>。</p><h3 id="in和not-in">3.6 in和not in</h3><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>[&#39;in&#39;, &#39;id&#39;, [1, 2, 3]] // 生成：id IN (1, 2, 3)[&#39;not in&#39;, &#39;id&#39;, [1, 2, 3]] // 生成：id NOT IN (1, 2, 3)</pre><div class="contentsignin">登入後複製</div></div><p style="white-space:pre-wrap;">运算符后面的<code class="hljs" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">运算数1</code>为列名称或DB表达式，<code class="hljs" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">运算数2</code>为数组，指定列值所在的范围。</p><p style="white-space:pre-wrap;">这个方法会为值添加引号，并正确转义。</p><p style="white-space:pre-wrap;">要生成混合<code class="hljs" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">IN</code>条件，列名和列值都设置为数组，并且用列名为列值指定下标：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>[&#39;in&#39;, [&#39;id&#39;, &#39;name&#39;], [[&#39;id&#39; => 1, &#39;name&#39; => &#39;foo&#39;], [&#39;id&#39; => 2, &#39;name&#39; => &#39;bar&#39;]]] // 生成：(`id`, `name`) IN ((1, &#39;foo&#39;), (2, &#39;bar&#39;))</pre><div class="contentsignin">登入後複製</div></div><p style="white-space:pre-wrap;">另外，还可以用子查询作为<code class="hljs" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">IN</code>条件的值，如下：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>[&#39;in&#39;, &#39;user_id&#39;, (new Query())->select(&#39;id&#39;)->from(&#39;users&#39;)->where([&#39;active&#39; => 1])]</pre><div class="contentsignin">登入後複製</div></div><h3 id="like">3.7 like</h3><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>[&#39;like&#39;, &#39;name&#39;, &#39;tester&#39;] // 生成：name LIKE &#39;%tester%&#39;[&#39;like&#39;, &#39;name&#39;, [&#39;test&#39;, &#39;sample&#39;]] // 生成：name LIKE &#39;%test%&#39; AND name LIKE &#39;%sample%&#39;[&#39;like&#39;, &#39;name&#39;, &#39;%tester&#39;, false] // 生成：name LIKE &#39;%tester&#39; // 这是自定义查询方式，要传入值为false的运算数3，并且自行添加%</pre><div class="contentsignin">登入後複製</div></div><p style="white-space:pre-wrap;"><span style="color:rgb(119,119,119);font-family:'courier new';"><span style="background-color:rgb(238,238,238);">运算数</span></span>后面的运算数1为列名称或DB表达式，运算数2为字符串或数组，指定列值查询条件。</p><p style="white-space:pre-wrap;">这个方法会为值添加引号，并正确转义。</p><p style="white-space:pre-wrap;"><code class="hljs coffeescript" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-keyword" style="color:rgb(167,29,93);">or</span> like</code>、<code class="hljs coffeescript" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-keyword" style="color:rgb(167,29,93);">not</span> like</code>、<code class="hljs coffeescript" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-keyword" style="color:rgb(167,29,93);">or</span><span class="hljs-keyword" style="color:rgb(167,29,93);">not</span> like</code>用法和<code class="hljs" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;">like</code>一样。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>[&#39;or like&#39;, &#39;name&#39;, [&#39;test&#39;, &#39;sample&#39;]] // 生成：name LIKE &#39;%test%&#39; OR name LIKE &#39;%sample%&#39;[&#39;not like&#39;, &#39;name&#39;, &#39;tester&#39;] // 生成：name NOT LIKE &#39;%tester%&#39;[&#39;or not like&#39;, &#39;name&#39;, [&#39;test&#39;, &#39;sample&#39;]] // 生成：name NOT LIKE &#39;%test%&#39; OR name NOT LIKE &#39;%sample%&#39;</pre><div class="contentsignin">登入後複製</div></div><h3 id="exists">3.8 exists</h3><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>[&#39;exists&#39;, (new Query())->select(&#39;id&#39;)->from(&#39;users&#39;)->where([&#39;active&#39; => 1])] // 生成：EXISTS (SELECT "id" FROM "users" WHERE "active"=1)</pre><div class="contentsignin">登入後複製</div></div></p> <p style="white-space:pre-wrap;"><code class="hljs perl" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-keyword" style="color:rgb(167,29,93);">not</span><span class="hljs-keyword" style="color:rgb(167,29,93);">exists</span></code>用法和<code class="hljs perl" style="font-family:Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:12px;padding:.3em;color:rgb(51,51,51);background-color:rgb(247,247,247);display:inline;line-height:1.65;"><span class="hljs-keyword" style="color:rgb(167,29,93);">exists</span></code>一样。</p> <p style="white-space: pre-wrap;"> 相关推荐：</p> <p style="white-space: pre-wrap;"><a href="http://www.php.cn/php-weizijiaocheng-330818.html" target="_self">Yii20中文开发向导——Where条件查询全解析</a></p>

以上是Yii2查詢where條件組裝詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！