First of all, thanks for your comment, the Bindable behavior is very
useful! Also, sorry for my very late response, I've been busy with
school and work.
Unfortunately the Bindable behavior doesn't help me with my problem :
(, although it made me think.
I thoughtI found a solution by using a hasOne-association in my Diner
model:
var $hasOne = array (
'Dinertotal' => array (
'className' => 'Diner',
'fields' => array (
'(SELECT SUM(count) FROM `dinersubscriptions` WHERE diner_id =
`Diner`.`id`) as `totalcount`',
'(SELECT SUM(amount) FROM `dinerpayments` WHERE diner_id =
`Diner`.`id`) as `totalamount`'
),
'foreignKey' => 'id',
),
);
This creates a JOIN query which retrieves the fields i want:
"SELECT `Diner`.`id`, `Diner`.`date`, `Diner`.`title`,
`Diner`.`description`, `Diner`.`stats`, `Diner`.`locked`,
`Diner`.`bill_id`, `Diner`.`created`, `Diner`.`modified`, `Bill`.`id`,
`Bill`.`date`, `Bill`.`created`, `Bill`.`modified`, (SELECT SUM(count)
FROM `dinersubscriptions` WHERE diner_id = `Diner`.`id`) as
`totalcount`, (SELECT SUM(amount) FROM `dinerpayments` WHERE diner_id
= `Diner`.`id`) as `totalamount` FROM `diners` AS `Diner` LEFT JOIN
`bills` AS `Bill` ON (`Diner`.`bill_id` = `Bill`.`id`) LEFT JOIN
`diners` AS `Dinertotal` ON (`Dinertotal`.`id` = `Diner`.`id`) WHERE
`Diner`.`id` = 499 LIMIT 1"
Array
(
[Diner] => Array
(
[id] => 1851
[date] => 2008-02-29
[title] =>
[description] =>
[stats] => 1
[locked] => 1
[bill_id] => 11
[created] => 2008-01-18 16:17:24
[modified] => 2008-03-01 23:04:18
)
[Bill] => Array
(
[id] => 11
[date] => 2008-03-20
[created] => 2008-03-16 16:29:18
[modified] => 2008-03-16 16:29:18
)
[0] => Array
(
[totalcount] => 5
[totalamount] => 8.6000003814697
)
)
I was happy to find this working for me, until I did a Bill-
>find('first') (where id = 11)!
It didn't work because cakePHP makes a lot of queries in order to
retrieve the hasOne assoc. (instead of a join):
SELECT `Diner`.`id`, `Diner`.`date`, `Diner`.`title`,
`Diner`.`description`, `Diner`.`stats`, `Diner`.`locked`,
`Diner`.`bill_id`, `Diner`.`created`, `Diner`.`modified` FROM `diners`
AS `Diner` WHERE `Diner`.`bill_id` IN (11)
SELECT (SELECT SUM(count) FROM `dinersubscriptions` WHERE diner_id =
`Diner`.`id`) as `totalcount`, (SELECT SUM(amount) FROM
`dinerpayments` WHERE diner_id = `Diner`.`id`) as `totalamount` FROM
`diners` AS `Dinertotal` WHERE `Dinertotal`.`id` = 1839
SELECT (SELECT SUM(count) FROM `dinersubscriptions` WHERE diner_id =
`Diner`.`id`) as `totalcount`, (SELECT SUM(amount) FROM
`dinerpayments` WHERE diner_id = `Diner`.`id`) as `totalamount` FROM
`diners` AS `Dinertotal` WHERE `Dinertotal`.`id` = 1840
etc...
This of course doesn't work because `Diner`.`id` is unknown.
So my question is: is there a way to force cakePHP to do a join query
instead of all those separate queries (it would also be a lot faster),
or can I some how make cakePHP query some extra fields? (Maybe by
making a behavior so you could define these fields in your Model).
I hope my story is still a little bit understandable, my English is
quite poor :(.
Yours sincerely,
Ruud Bijnen
On Mar 15, 11:34 pm, "b logica" <[EMAIL PROTECTED]> wrote:
> Pancakes, yum!
>
> If you haven't already, check out Mariano's article on his Bindable
> behavior. I found a good way to play around with it was to put
> debug($my_whatever) in the view and to then play around with
> restrictions in the controller.
>
> http://bakery.cakephp.org/articles/view/bindable-behavior-control-you...
>
>
>
> On Sat, Mar 15, 2008 at 3:26 PM, Maff^ <[EMAIL PROTECTED]> wrote:
>
> > Hi There,
>
> > First of all I want to state that I'm not a very advanced CakePHP
> > user. I tried to find answers to my question here and with Google, but
> > I'm probably not using there right keywords.
>
> > Okay, here is my situation: I have an Diner model which has 2 hasMany
> > associations (to Dinersubscription and Dinerpayment). The
> > Dinersubscriprion model has a field 'count' telling how many servings
> > a Resident wants. (0 meaning that person won't join diner). The
> > Dinerpayment model tels who paid for the groceries and adding up all
> > records for a particular diner gives you the costs for that diner.
>
> > Here is a image illustration my situation:
> > http://bl-tyrone.student.utwente.nl/~Mafioso/cakephp/Diner_structure.gif
>
> > diner.php:
> > <?php
> > class Diner extends AppModel {
>
> > var $name = 'Diner';
> > var $displayField = 'date';
>
> > var $hasMany = array(
> > 'Dinersubscription' => array('className' =>
> > 'Dinersubscription',
> > 'foreignKey' => 'diner_id',
> > 'Dinerpayment' => array('className' => 'Dinerpayment',
> > 'foreignKey'
> > => 'diner_id'),
> > );
>
> > var $belongsTo = array ('Bill');
>
> > }
> > ?>
>
> > Using sub queries I can lookup the costs an total count for a diner:
> > SELECT Diner.*, (SELECT SUM(count) FROM dinersubscriptions WHERE
> > diner_id = 1717) as totalcount, (SELECT SUM(amount) FROM dinerpayments
> > WHERE diner_id = 1717) as totalamount FROM diners as Diner WHERE id =
> > 1717;
>
> > My question is the following: Is it possible to add these subqueries
> > to the associations of the Diner model, so every time I look up a
> > diner (or more diners at once) I retrieve the total count of
> > Dinersubscriptions and the total amount of Dinerpayments?
>
> > I've tried to add hasOne-associations but that didn't work :(
>
> > Btw, I'm using CakePHP version 1.2.0.5427alpha. And I've removed the
> > Bill model association because it's inrelevant.
>
> > Greetings, Ruud Bijnen- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" 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
-~----------~----~----~----~------~----~------~--~---
