CakePHP：如何使用“HAVING”使用find方法构建查询时的操作？ [英] CakePHP: How can I use a "HAVING" operation when building queries with find method?

问题描述

我试图在使用CakePHP paginate（）方法的SQL查询中使用HAVING子句。

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

在进行一些搜索后，不能通过Cake的paginate（）/ find（）方法来实现。

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

代码我看起来像这样：

$this->paginate = array(
        'fields' => $fields,
        'conditions' => $conditions,
        'recursive' => 1,
        'limit' => 10, 
        'order' => $order,
        'group' => 'Venue.id');

$字段之一是别名distance。我想添加一个查询， 25（例如HAVING距离<25）。

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

到目前为止，我已经看到了两种解决方法，不幸的是，这两种解决方案都不适合我的需要。我看到的两个是：

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

1）在group选项中添加HAVING子句。例如'group'=> 'Venue.id HAVING distance< 25'。当与分页结合使用时，它似乎无法正常工作，因为它会弄乱执行的初始计数查询。 （即尝试 SELECT distinct（Venue.id HAVING distance <25），这显然是无效语法。

1) Adding the HAVING clause in the "group" option. e.g. 'group' => 'Venue.id HAVING distance < 25'. 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 SELECT distinct(Venue.id HAVING distance < 25) which is obviously invalid syntax.

2）在WHERE条件之后添加HAVING子句（例如 WHERE 1 = 1 HAVING字段> 25 ）这不工作，因为看来HAVING子句必须 之后放置 组织语句。

2) Adding the HAVING clause after the WHERE condition (e.g. WHERE 1 = 1 HAVING field > 25) 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.

有人知道如何使用CakePHP的find （） 方法？我不想使用query（），因为这将涉及很多返工，也意味着我需要实现自己的分页逻辑！

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!

提前感谢

推荐答案

您必须将其与组条件一起使用。像这样

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

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

希望它能帮助你

这篇关于CakePHP：如何使用“HAVING”使用find方法构建查询时的操作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！