converting sql query into cakephp format

Question

Hi

i need the following sql query into cakephp find() format. the query it self is working fine but i need to change it.

$this->Part->query(
    "SELECT `parts`.`id`,`parts`.`part_name` 
     FROM `parts`  
     LEFT JOIN ( 
         SELECT `op` . * 
         FROM `order_parts` AS `op` 
         WHERE `op`.`order_id` =".$this->Session->read('orderid')."
     ) AS `vT` 
     ON ( `parts`.`id` = `vT`.`part_id` )
     WHERE `vT`.`part_id` IS NULL"
);

thanks

parts to order_parts is 1 - many relationship, i just need to get the part_id and part_name from the parts table. also there is another table involved which is order, and the order has a 1 to many relationship to order_parts. so order to order_parts is 1 to many — Tman
– Tman, Commented Oct 10, 2012 at 16:01
Maybe I'm wrong, but the relationship you should have between parts and orders would not be HABTM which is done through the table orders_parts? — Paulo Rodrigues
– Paulo Rodrigues, Commented Oct 10, 2012 at 16:19
Post your Parts and Order relationships. Do you have OrderPart model? — bancer
– bancer, Commented Oct 10, 2012 at 22:12
parts and orders has a many to many relationship, and when i broke it down i get the orderparts as the middle table — Tman
– Tman, Commented Oct 11, 2012 at 1:48

xSphere · Accepted Answer · 2012-10-12 21:01:37Z

1

If your relationship are Order HABTM Part and you have a table orders_parts with columns: id, order_id,part_id you should be able to do something like this:

First, get the ids of the parts which are in the order:

//each Part has one OrdersPart per order
$this->Part->bindModel(array('hasOne' => array('OrdersParts')));
$parts = $this->Part->find('list', array(
  'fields' => array('Part.name'),
  'conditions' => array(
    'OrdersParts.order_id' => $this->Session->read('orderid'),
  ),
  'recursive' => 2
));

Now get the parts which are not in the order:

$this->Part->find('all', array(
  'conditions' => array(
    "NOT" => array('Part.id' => array_keys($parts))
  ),
));

edited Oct 12, 2012 at 21:01

answered Oct 12, 2012 at 10:46

xSphere

863 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tman Over a year ago

Hi Xsphere: let say i have orderid=2; my query above is asking the database give me all the parts from the part table which does not exists in the the order_parts table for orderid =2. now your query is getting all the parts that exists in the order_parts table for orderid=2. hope this make sense

xSphere Over a year ago

Oops @TimorMangal- I should have read your query more carefully. In that case I would do two queries: first do a find('list'...) as per my first answer to get the ids of the parts which are in the order. Then do another query to get the parts which don't have those ids. I'll amend my answer.

Collectives™ on Stack Overflow

converting sql query into cakephp format

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related