Most efficient way to join tables

I have two models in cakePHP, one is Parts and the other is Orders.

Parts is a simple model with an id column plusaction multiple fields such as customer, part number, description etc. Each part has its own unique id.

Orders is another simple model with a field called *part_id* which represents an id from the Parts model.

In an action, in the Orders controller I wll need to pull all the orders for that particualr view, find information about that order from the Parts database and put it together in an array ready to be passed to the view.

For example that tables might look like:

ORDERS

Part_id   id
2         5
1         7

PARTS

customer  partnumber  description    id
Google    XHA-V1      wi开发者_运维技巧dget_1       1
Yahoo     YVS-XN      widget_5       2

Since I will have 50-100 orders in any one view, I need an efficient way to reference each order's *part_id* in the Parts database and combine this into one array. Its the searching of the Parts database multiple times that I'm unsure how to achieve effectively.

Note: I guess the solution could be cake or pure PHP.

---

If you have correctly defined the relation in your Order Model, you should be able to retreive all the corresponding Parts

$this->Orders->find('all');

cakephp will do it in two queries: - a SELECT for all the Orders - a SELECT for all the Products where product.id in ( list_of_Orders_ids )

and then it will contruct an array in a proper format

If you need an special query, you could also "manually" set the joins for the query, but you'll need to set the recursive to -1:

$this->Orders->find('all',array('recursive'=>-1,
                                'fields'=>array('Order.*','Part.*'),
                                'joins'=>array(array('table' => 'parts',
                                                     'alias' => 'Part',
                                                     'type' => 'INNER', //or left
                                                     'conditions' => array('Part.id = Order.part_id')))))

Good Luck!

---

You can get an array of (all) Orders using the $this->Order->find('all', array('recursive' => 2)); in your controller. Each order also contains the related Parts information.

Imo customer information should be part of the Order, not of Part. Also you might want Orders to consist of multiple Parts.