blocks|key|250865|text|你必须把它和团队的条件放在一起。像这样|type|unstyled|depth|inlineStyleRanges|entityRanges|data|250866|$this->find('all',+array(
++++'conditions'+=>+array(
++++++++'Post.length+>='+=>+100
++++),
++++'fields'+=>+array(
++++++++'Author.id',+'COUNT(*)+as+Total'
++++),
++++'group'+=>+array(
++++++++'Total+HAVING+Total+>+10'
++++)
));|code-block|syntax|javascript|250867|希望能对你有所帮助|250868|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You have to put it with the group conditions. like this

<pre><code>$this-&gt;find('all', array(
 'conditions' =&gt; array(
 'Post.length &gt;=' =&gt; 100
 ),
 'fields' =&gt; array(
 'Author.id', 'COUNT(*) as Total'
 ),
 'group' =&gt; array(
 'Total HAVING Total &gt; 10'
 )
));
</code></pre>

Hope it helps you

blocks|key|3146182|text|只是遇到了同样的问题。我知道，不应该修改内部代码，但如果您打开PaginatorComponent并修改了第188行：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3146183|$count+=+$object->find('count',+array_merge($parameters,+$extra));|code-block|syntax|javascript|3146184|要这样做：|3146185|$count+=+$object->find(
+++++++++++++'count',+
+++++++++++++array_merge(array("fields"+=>+$fields),$parameters,+$extra)
+++++++++);|3146186|一切都会好起来的。您将能够将您的HAVING子句添加到'group‘中，计数(*)不会成为问题。|3146187|或者，生成命令行：|3146188|$count+=+$object->paginateCount($conditions,+$recursive,+$extra);|3146189|包含$fields的步骤|3146190|$count+=+$object->paginateCount($fields,$conditions,+$recursive,+$extra);|3146191|之后，您可以“覆盖”模型上的方法，并确保在find()中包含$fields，就是这样！，=P|3146192|entityMap^0|V|I|0|0|0|0|0|0|0|2|7|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|15|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|16|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|17|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|18|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|19|8|@]|D|@]|E|$]]|$1|S|3|T|5|H|7|1A|8|@]|D|@]|E|$I|J]]|$1|U|3|V|5|6|7|1B|8|@$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|W|3|X|5|H|7|1E|8|@]|D|@]|E|$I|J]]|$1|Y|3|Z|5|6|7|1F|8|@]|D|@]|E|$]]|$1|10|3|-4|5|6|7|1G|8|@]|D|@]|E|$]]]|11|$]]

Just had the same problem. I know, one is not supposed to modify the internal code but if you open the <code>PaginatorComponent</code> and you modify line 188:

<pre><code>$count = $object-&gt;find('count', array_merge($parameters, $extra));
</code></pre>

to this:

<pre><code>$count = $object-&gt;find(
 'count', 
 array_merge(array("fields" =&gt; $fields),$parameters, $extra)
 );
</code></pre>

Everything will be fixed. You will be able to add your HAVING clause to the 'group' and the COUNT(*) won't be a problem.

Or, make line:

<pre><code>$count = $object-&gt;paginateCount($conditions, $recursive, $extra);
</code></pre>

to include the <code>$fields</code>:

<pre><code>$count = $object-&gt;paginateCount($fields,$conditions, $recursive, $extra);
</code></pre>

After that, you can "override" the method on the Model and make sure to include the $fields in the find() and that's it!, =P

blocks|key|249314|text|我使用以下技巧在WHERE子句的末尾添加了我自己的HAVING子句。蛋糕sub-query+documentation中提到了"dbo->expression()“方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|249315|function+addHaving(array+$existingConditions,+$havingClause)+{
++$model+=+'User';
++$db+=+$this->$model->getDataSource();

++//+Two+fun+things+at+play+here,
++//+1+-+mysql+doesn't+allow+you+to+use+aliases+in+WHERE+clause
++//+2+-+Cake+doesn't+allow+a+HAVING+clause+separate+from+a+GROUP+BY
++//+This+expression+should+go+last+in+the+WHERE+clause+(following+the+last+AND)
++$taut+=+count($existingConditions)+>+0+?+'1+=+1'+:+'';
++$having+=+$db->expression("$taut+HAVING+$havingClause");

++$existingConditions[]+=+$having;

++return+$existingConditions;
}|code-block|syntax|javascript|249316|entityMap|0|LINK|mutability|MUTABLE|url|http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#sub-queries^0|10|N|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

I used the following trick to add my own HAVING clause at the end of my WHERE clause. The "dbo->expression()" method is mentioned in the cake <a href="http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#sub-queries" rel="nofollow">sub-query documentation</a>.

<pre class="lang-php prettyprint-override"><code>function addHaving(array $existingConditions, $havingClause) {
 $model = 'User';
 $db = $this-&gt;$model-&gt;getDataSource();

 // Two fun things at play here,
 // 1 - mysql doesn't allow you to use aliases in WHERE clause
 // 2 - Cake doesn't allow a HAVING clause separate from a GROUP BY
 // This expression should go last in the WHERE clause (following the last AND)
 $taut = count($existingConditions) &gt; 0 ? '1 = 1' : '';
 $having = $db-&gt;expression("$taut HAVING $havingClause");

 $existingConditions[] = $having;

 return $existingConditions;
}
</code></pre>

blocks|key|249333|text|这是另一个没有解决分页问题的想法，但它是干净的，因为它只是覆盖了AppModel中的find命令。只需向您的查询中添加组和having元素，这将转换为HAVING子句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|249334|public+function+find($type+=+'first',+$query+=+array())+{
++++if+(!empty($query['having'])+&&+is_array($query['having'])+&&+!empty($query['group']))+{
++++++if+($type+==+'all')+{
++++++++if+(!is_array($query['group']))+{
++++++++++$query['group']+=+array($query['group']);
++++++++}

++++++++$ds+=+$this->getDataSource();
++++++++$having+=+$ds->conditions($query['having'],+true,+false);
++++++++$query['group'][count($query['group'])+-+1]+.=+"+HAVING+$having";

++++++++CakeLog::write('debug',+'Model->find:+out+query='+.+print_r($query,+true));
++++++}+else+{
++++++++unset($query['having']);
++++++}
++++}

++++return+parent::find($type,+$query);
++}|code-block|syntax|javascript|249335|在这里找到的|249336|https://groups.google.com/forum/?fromgroups=#!topic/tickets-cakephp/EYFxihwb55I|offset|length|249337|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|27|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|V|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|W|8|@]|9|@$K|X|L|Y|1|Z]]|A|$]]|$1|M|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|J]]]]

Here is another idea that doesn't solve the pagination issue, but it is clean since it just overrides the find command in AppModel. Just add a group and having element to your query and this will convert to a HAVING clause.

<pre><code>public function find($type = 'first', $query = array()) {
 if (!empty($query['having']) &amp;&amp; is_array($query['having']) &amp;&amp; !empty($query['group'])) {
 if ($type == 'all') {
 if (!is_array($query['group'])) {
 $query['group'] = array($query['group']);
 }

 $ds = $this-&gt;getDataSource();
 $having = $ds-&gt;conditions($query['having'], true, false);
 $query['group'][count($query['group']) - 1] .= " HAVING $having";

 CakeLog::write('debug', 'Model-&gt;find: out query=' . print_r($query, true));
 } else {
 unset($query['having']);
 }
 }

 return parent::find($type, $query);
 }
</code></pre>

Found it here

<a href="https://groups.google.com/forum/?fromgroups=#!topic/tickets-cakephp/EYFxihwb55I" rel="nofollow">https://groups.google.com/forum/?fromgroups=#!topic/tickets-cakephp/EYFxihwb55I</a>

blocks|key|252887|text|在find中使用'having‘对我不起作用。相反，我将“group+=>+product_id，color_id+having+sum(quantity)+>+2000”组放入一个字符串中，效果很好。使用CakePHP+2.9|type|unstyled|depth|inlineStyleRanges|entityRanges|data|252888|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Using 'having' in find did not work for me. Instead I put into one string with the group
&quot; group =&gt; product_id, color_id having sum(quantity) &gt; 2000 &quot; and works like a charm.
Using CakePHP 2.9

I'm trying to use the "HAVING" clause in a SQL query using the CakePHP paginate() method.

After some searching around it looks like this can't be achieved through Cake's paginate()/find() methods. 

The code I have looks something like this:

<pre><code>$this-&gt;paginate = array(
 'fields' =&gt; $fields,
 'conditions' =&gt; $conditions,
 'recursive' =&gt; 1,
 'limit' =&gt; 10, 
 'order' =&gt; $order,
 'group' =&gt; 'Venue.id');
</code></pre>

One of the $fields is an alias "distance". I want to add a query for when distance &lt; 25 (e.g. HAVING distance &lt; 25).

I have seen two workarounds so far, unfortunately neither suit my needs. The two I've seen are:

1) Adding the HAVING clause in the "group" option. e.g. <code>'group' =&gt; 'Venue.id HAVING distance &lt; 25'</code>. This doesn't seem to work when used in conjunction with pagination as it messes up the initial count query that is performed. (ie tries to <code>SELECT distinct(Venue.id HAVING distance &lt; 25)</code> which is obviously invalid syntax.

2) Adding the HAVING clause after the WHERE condition (e.g. <code>WHERE 1 = 1 HAVING field &gt; 25</code>) This doesn't work as it seems the HAVING clause must come after the group statement which Cake is placing after the WHERE condition in the query it generates.

Does anyone know of a way to do this with CakePHP's find() method? I don't want to use query() as that would involve a lot of rework and also mean I'd need to implement my own pagination logic!

Thanks in advance

CakePHP: How can I use a "HAVING" operation when building queries with find method?

我正在尝试使用CakePHP paginate()方法在SQL查询中使用"HAVING“子句。经过一番搜索，这看起来不能通过Cake的paginate()/find()方法来实现。我的代码看起来像这样：$this->paginate = array(        'fields' => $fields,       ...

问CakePHP:在使用find方法构建查询时，如何使用"HAVING“操作？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问CakePHP:在使用find方法构建查询时，如何使用"HAVING“操作？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问CakePHP:在使用find方法构建查询时，如何使用"HAVING“操作？
EN