Minimum and maximum field value in cakephp and mysql

Question

I am trying to create a search function for cakephp and mysql site. Selecting various parameters, such as product price or length, calls an ajax call, which returns the number of matching results. I want to expand the returned results with minimum and maximum values ​​for lengths and prices. I tried to do this, http://bin.cakephp.org/view/1004813660 . Using the first 4 finds is too laborious. The latter works locally, but I get an error:

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

remotely due to ONLY_FULL_GROUP_BY inclusion.

Can I use the latter with some improvements, or can I disable ONLY_FULL_GROUP_BY?

Answer 1

I decided to solve the problem by changing the search method. Instead of making queries in conditions that lead to unions, I explicitly searched where. I had things like

$conditions = array('Brand.name LIKE'=> '%bla%'); 

which I replaced with

  $condtions = array('Yacht.brand_name LIKE' => '%bla%'); 

I had to rebuild the database a bit, but the trade-off between speed and database normalization is the one I can live with.

Answer 2

If I understand you well, you want to get one 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?

You do not need any "Group By" sentence for this. The MIN and MAX functions are already aggregation functions. But nothing prevents you from using multiple aggregation functions in a single request.

Have you tried just 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 a lot of redundancy in your source code. "find (" first ", array (...))" by itself guarantees that you get only one result, therefore, there is no need to specify "limit" => 1 "in the query or" order ", since it should still there is only one field :)

Hope this helps.

Answer 3

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 default settings for the server:

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

However, I am not sure if this is necessary to solve your decision. I think your query has been working for a long time because you need a different group in your code and fewer queries. You should be able to use a logical group that maximizes 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' => '...'));