For anyone interested I solved/got around this problem myself see https://trac.cakephp.org/ticket/2457
*/Jeremy Pointer/* [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Jeremy Pointer wrote: > 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 -~----------~----~----~----~------~----~------~--~---