Hi,
first of all I am a newbie so this might be a very easy question. But
not for me ;-)
How can I retrieve data with the find method while grouping and
retrieving the maximum value of a second table? The beginning of the
Controller class:
class InvoicesController extends AppController {
var $name = 'Invoices';
var $helpers = array('Html', 'Form');
var $uses = array('Installation');
function index() {
$fields = array('Installation.name', 'MAX(Invoice.period_end)' );
$group = array('Installation.id');
$installations = $this->Installation->find('all', array (
'fields' => $fields,
'group' => $group ));
$this->set('installations', $installations );
}
When I call this I got the error message:
*SQL Error:* 1054: Unknown column 'Invoice.period_end' in 'field list'
[*CORE/cake/libs/model/datasources/dbo_source.php*, line *525*]
Code <javascript:void(0);> | Context <javascript:void(0);>
$sql = "SELECT `Installation`.`name`, MAX(`Invoice`.`period_end`),
`Installation`.`id` FROM `tom_installations` AS `Installation` WHERE
`Installation`.`active` = 1 GROUP BY `Installation`.`id` "
$error = "1054: Unknown column 'Invoice.period_end' in 'field list'"
$out = null
| $out = null;|
| if ($error) {|
| trigger_error("<span style =
\"color:Red;text-align:left\"><b>SQL Error:</b> {$this->error}</span>",
E_USER_WARNING);|
DboSource::showQuery() - CORE/cake/libs/model/datasources/dbo_source.php, line
525
DboSource::execute() - CORE/cake/libs/model/datasources/dbo_source.php, line 201
DboSource::fetchAll() - CORE/cake/libs/model/datasources/dbo_source.php, line
337
DboSource::read() - CORE/cake/libs/model/datasources/dbo_source.php, line 647
Model::find() - CORE/cake/libs/model/model.php, line 1961
InvoicesController::index() - APP/controllers/invoices_controller.php, line 22
Object::dispatchMethod() - CORE/cake/libs/object.php, line 115
Dispatcher::_invoke() - CORE/cake/dispatcher.php, line 227
Dispatcher::dispatch() - CORE/cake/dispatcher.php, line 194
[main] - APP/webroot/index.php, line 88
*Query:* SELECT `Installation`.`name`, MAX(`Invoice`.`period_end`),
`Installation`.`id` FROM `installations` AS `Installation` WHERE
`Installation`.`active` = 1 GROUP BY `Installation`.`id`
So cake does not include the Invoice table in the sql query.
So how can I do this?
Thanks a lot in advance.
Best regards,
Roman
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
