Yes, I noticed that, but it is a typo. In the original source the
second variable is wd_amount. :) Thanks anyway :)
On Oct 16, 8:02 am, Bert Van den Brande <cyr...@gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
