Your English is far better than my Dutch (Oranje Boven!) I wonder if this thread will be helpful to your sutuation as well:
http://groups.google.com/group/cake-php/browse_frm/thread/f7508c11b6f785ff# On Tue, Apr 1, 2008 at 5:07 PM, Maff^ <[EMAIL PROTECTED]> wrote: > > 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 -~----------~----~----~----~------~----~------~--~---
