Don't know if it helps but you map two sums to the same column alias
'amount' , I think you should make them unique ...
On Thu, Oct 15, 2009 at 3:13 PM, logout <stefano...@gmail.com> wrote:
>
> 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 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
-~----------~----~----~----~------~----~------~--~---
