Minimum and maximum of a field in cakephp and mysql

Question

I am trying to build a search function for a cakephp and mysql site. Selecting different parameters like price of the product or the length triggers an ajax call which returns the number of matching results. I want to extend the returned results with the the minimum and maximum values for the lengths and prices. I tried doing this, http://bin.cakephp.org/view/1004813660 . Using the first 4 finds is too time consuming. The last one functions locally, but I get the error;

1140 - Mixing of GROUP columns (MIN(),MAX(),,...) with no GROUP columns is illegal if there is no GROUP BY clause`

remotely, due to ONLY_FULL_GROUP_BY being on.

Is it possible to use the last option with some improvements, or can I switch off ONLY_FULL_GROUP_BY?

Answer 1

If I understood you well, you want to get in a single request

• MIN(Yacht.price) as min_price
• MAX(Yacht.price) as max_price
• MIN(Yacht.long) as min_length
• MAX(Yacht.long) as max_length

right ?

For this, you do not need any "Group By" clause. MIN and MAX functions are already aggregations functions. But nothing prevents you from using multiple aggregations functions in a single request.

Have you tried simply doing this ?

$stats = $this->Yacht->find(array(
    'conditions' => $conditions,
    'fields' => array(
        'MIN(Yacht.price) as min_price',
        'MAX(Yacht.price) as max_price',
        'MIN(Yacht.long) as min_length',
        'MAX(Yacht.long) as max_length'
     )
    )
);

By the way, according to the documentation, there seems to be quite a lot of redundancy in your original code. "find('first', array(...))" by itself ensures you get only one result hence, there is no need to specify "'limit' => 1" in the request nor "order" clause as there would be only one field anyway :)

Hope it helps.

Answer 2

The way to set server modes can be found here... If you read the top of the document it will tell you how to set the server mode defaults:

http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

However, I'm not sure that is necessary to get to your solution. I think your query is running for a long time because you need a different group by in your code and less queries. You should be able to use a logical group by that will maximize your primary key (index):

'group' => 'Yacht.id'

So you have one query returning everything:

$this->Yacht->find('first', array(
'conditions' => $conditions,
'fields' => array('MAX(Yacht.price) as max_price', 'MIN(Yacht.price) as min_price', ...)
'group' => 'Yacht.id'
'order' => '...'));