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'"),
);
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
--
*/Jeremy Pointer/*
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
