I don't know what it is guys but every one of my questions I've asked so far seems to take a fair while to get answered (Maybe I'm asking them wrong, or maybe my problems are too complicated (or too stupid) and yes I know we are all volunteers here so I'm not complaining just wondering if there is something I can do to make my questions clearer), anyway as it is I did spot a few bits of fog in last nights question when I reread it, so I'm replying to myself to clarify a little. Comments in between original question.
*/Jeremy Pointer/* [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Jeremy Pointer wrote: > In my orders controller I have the following (The idea is to use the > orders index view to show a list of orders for a contact, the contact > could be a patient or an Audiologist (If it's an audiologist they might > be the dispensing or delivery audiologist (don't ask I also thought it > was madness but anyway thats what the client needs)) I call this with a > requestAction in the view. Mainly because I can't quite figure how to do > this properly in the model for contacts I have the Orders model set up > with BelongsTo for each contact type but my brain ain't working to work > the other way around. > belongsTo of order.php model > var $belongsTo = array( > > 'DispensingPractice'=>array('className'=>'Practice','foreignKey'=>'disp_practice_id',), > 'DispensingAudiologist'=>array('className'=>'Contact', > > 'foreignKey'=>'disp_contact_id', > > 'conditions'=>"`DispensingAudiologist`.`type`='Audiologist'"), > > 'DeliveryPractice'=>array('className'=>'Practice','foreignKey'=>'deliv_practice_id'), > > 'DeliveryAudiologist'=>array('className'=>'Contact','foreignKey'=>'deliv_contact_id', > > 'conditions'=>"`DeliveryAudiologist`.`type`='Audiologist'"), > > 'Patient'=>array('className'=>'Contact','foreignKey'=>'patient_id', > > 'conditions'=>"`Patient`.`type`='Patient'"), > ); > > In my orders_controller I have : > function OrdersByContact($id) { > $this->Order->recursive = 0; > > $conditions=array('OR'=>array("Order.patient_id"=>$id,"Order.disp_contact_id"=>$id,"Order.deliv_contact_id"=>$id)); > > $orders=$this->paginate('Order',array('conditions'=>$conditions)); > if (!isset($type) || $type=='') $type='%'; > $this->set(compact('orders','type')); > $this->render('index'); > } > > This generates the count query as below and a similar full query, > unfortunately mysql is choking on this query I have to kill the process: > > SELECT COUNT(*) AS count FROM `orders` AS `Order` LEFT JOIN `practices` > AS `DispensingPractice` ON (`Order`.`disp_practice_id` = > `DispensingPractice`.`id`) LEFT JOIN `contacts` AS > `DispensingAudiologist` ON (`DispensingAudiologist`.`type`='Audiologist' > AND `Order`.`disp_contact_id` = `DispensingAudiologist`.`id`) LEFT JOIN > `practices` AS `DeliveryPractice` ON (`Order`.`deliv_practice_id` = > `DeliveryPractice`.`id`) LEFT JOIN `contacts` AS `DeliveryAudiologist` > ON (`DeliveryAudiologist`.`type`='Audiologist' AND > `Order`.`deliv_contact_id` = `DeliveryAudiologist`.`id`) LEFT JOIN > `contacts` AS `Patient` ON (`Patient`.`type`='Patient' AND > `Order`.`patient_id` = `Patient`.`id`) LEFT JOIN `repairorders` AS > `Repairorder` ON (`Repairorder`.`order_id` = `Order`.`id` AND ( > `Order`.`patient_id` = 594) OR ( `Order`.`disp_contact_id` = 594) OR ( > `Order`.`deliv_contact_id` = 594)) LEFT JOIN `hearingaidorders` AS > `Hearingaidorder` ON (`Hearingaidorder`.`order_id` = `Order`.`id` AND ( > `Order`.`patient_id` = 594) OR ( `Order`.`disp_contact_id` = 594) OR ( > `Order`.`deliv_contact_id` = 594)) WHERE (`Order`.`patient_id` = 594) OR > (`Order`.`disp_contact_id` = 594) OR (`Order`.`deliv_contact_id` = 594) > > If however I remove the additional conditions from the JOIN ON's and > only keep them in the WHERE and run the query manually mysql does not > choke.So I wonder : > a) why does paginate generate the conditions in every JOIN clause > b) Is there a way for me to remove them. > c) Should I set up the model better ( I haven't got a contact->orders > relationship set up in my contact model if I wanted to what would it > need to look like.) > d) should that belongsTo be a hasOne? would it make any difference > > In answer to d) It does make a difference and it won't work that way --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---
