heisenberg heisenberg - 1 year ago 133
SQL Question

Select 2 columns with CakePHP 2

Excuse me if the title doesn't suit my question.

I have a table named Sales and 3 columns named Seller, Buyer and Price.

What I have :

What I want :

Basically I want to display a list of sellers with the sum of the sales they have done with one buyer.

This is the SQL query I'm going with :

SELECT seller, buyer ,SUM(price)
FROM sales
GROUP BY seller , buyer

My problem (besides that this query is probably wrong) is the "translation" into CakePHP. After going through the cookbook, I have this in my controller :

public function myview (){
$this->set('mysales', $this->Sales->find('all',
array('sum(price) AS total','group' => array('seller','buyer'))));

I display the results in myview.ctp into a HTML table with PHP (I didn't put the HTLM elements below):

foreach ($mysales as $thesales)
echo $thesales['Sales']['seller'];
echo $thesales['Sales']['buyer'];
echo $thesales['Sales']['total'];

Of course it doesn't work. I'm working with SQL Server 2014 and I have this error :

Error: SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Column 'sales.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thanks very much for your help !

Answer Source

For SQL functions, you should follow this syntax: Using SQL Functions

Try this:

For CakePHP 3:

$mysales =  $this->Sales->find();

$mysales =  $mysales 
        'seller', 'buyer',
        'total_price' => $mysales ->func()->sum('price'),

pr($mysales); // Check your result

For CakePHP 2.x:

$this->set('mysales', $this->Sales->find('all', 
     'fields' => array('SUM(price) AS total','seller','buyer'),
     'group' => array('seller','buyer')

In your view:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download