Oh, I tried with the Containable behavior, but without any luck...
On Oct 16, 11:04 am, "Dr. Loboto" <[email protected]> wrote:
> Not any helpful reply, but... your query will work with "Invoice
> hasOne Writedown".
> And you can try Containable
> behavior:http://book.cakephp.org/view/474/Containable
>
> On Oct 15, 8:13 pm, logout <[email protected]> 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 [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
-~----------~----~----~----~------~----~------~--~---
