Guys,
I have a problem. I tried to write one SQL query useing the CakePHP
style, but I failed. Can someone help?
Here are the tree models:
1. "Currency" with fields:
id
name
2. "Invoice" with fields
id
amount
currency_id (the FK)
... some other fields
3. "Writedown" with fields
id
amount
invoice_id (the FK)
... some other fields
The relations between these models are:
Currency hasMany Invoice
Invoice hasMany Writedown
Invoice belongsTo Currency
Writedown belongsTo Invoice
Now I want to get the sums of the amounts of all invoices and all
writedowns and grouping them by the currency
In the "Invoices" controller I use this query to do the job:
$sums = $this->Invoice->query(
'SELECT `Currency`.`name`, `Currency`.`id`,
SUM(`Invoice`.`amount`)
as amount, SUM(`Writedown`.`amount`) as wd_amount
FROM `currencies` as `Currency`, `invoices` as
`Invoice`,
`writedowns` as `Writedown`
WHERE `Invoice`.`id` = `Writedown`.`invoice_id` AND
`Invoice`.`currency_id` = `Currency`.`id` GROUP BY `Currency`.`id`'
);
So I get this result:
Array
(
[0] => Array
(
[Currency] => Array
(
[name] => EUR
[id] => 1
)
[0] => Array
(
[amount] => 1000
[wd_amount] => 0
)
)
[1] => Array
(
[Currency] => Array
(
[name] => USD
[id] => 2
)
[0] => Array
(
[amount] => 1500
[wd_amount] => 300
)
)
)
and it is exactly what I want.
I tried to use the find('All') function:
$sums = $this->Invoice->find('all', array(
'conditions' => $invoiceConditions,
'fields'=>array('Currency.name', 'SUM(Invoice.amount)
as amount',
'SUM(Writedown.amount) as amount'),
'group' => array('Currency.id'),
'order' => array('Currency.id')
)
);
(The $invoiceConditions contains some conditions for period and other
stuff)
but without any success. The error is "SQL Error: 1054: Unknown column
'Writedown.amount' in 'field list'"
Whatever I try, it doesn't work. I can live with the query(), but I
want to know how can I write it in the CakePHP style.
I also looked at the resulting query and found that it left joins only
the Currency model and it doesn't do anything about the Writedown
model.
If I remove the 'SUM(Writedown.amount) as amount' field, I get this
result:
Array
(
[0] => Array
(
[Currency] => Array
(
[name] => EUR
[id] => 1
)
[0] => Array
(
[amount] => 1000
)
)
[1] => Array
(
[Currency] => Array
(
[name] => USD
[id] => 2
)
[0] => Array
(
[amount] => 1500
)
)
)
just to show you that it works at some point.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
