On Wed, Nov 17, 2010 at 10:22 AM, Matthias <[email protected]> wrote: > Hi all, > I'm trying to perform a search in some fields of associated tables. I > have an orders table for the order data, an items table for the items > the orders contain of, and a table connecting them (order parts). > > So the models say: > Item: $hasMany = 'OrderPart' > Order: $hasMany = 'OrderPart' > OrderPart: $belongsTo = array('Order', 'Item'); > Right? > > To retrieve all orders i do: > $all_orders = $this->Order->find('all', array( > 'contain' => array( > 'Customer', > 'OrderPart' => 'Item.name' > ) > )); > > The result is fine, although the SQL looks a bit dirty (the example > below is for only 2 entries): > SELECT `Order`.`id`, `Order`.`customer_id`, `Order`.`date_of_pickup`, > `Order`.`date_of_return`, `Order`.`total_lending_fee`, > `Order`.`currency`, `Order`.`status`, `Order`.`created`, > `Order`.`modified`, `Customer`.`id`, `Customer`.`name`, > `Customer`.`company`, `Customer`.`street_address`, > `Customer`.`postcode`, `Customer`.`city`, `Customer`.`country`, > `Customer`.`email`, `Customer`.`telephone`, `Customer`.`created`, > `Customer`.`modified` FROM `orders` AS `Order` LEFT JOIN `customers` > AS `Customer` ON (`Order`.`customer_id` = `Customer`.`id`) WHERE 1 = 1 > ORDER BY `Order`.`id` ASC > > SELECT `Customer`.`id`, `Customer`.`name`, `Customer`.`company`, > `Customer`.`street_address`, `Customer`.`postcode`, `Customer`.`city`, > `Customer`.`country`, `Customer`.`email`, `Customer`.`telephone`, > `Customer`.`created`, `Customer`.`modified` FROM `customers` AS > `Customer` WHERE `Customer`.`id` = 1 > > SELECT `Customer`.`id`, `Customer`.`name`, `Customer`.`company`, > `Customer`.`street_address`, `Customer`.`postcode`, `Customer`.`city`, > `Customer`.`country`, `Customer`.`email`, `Customer`.`telephone`, > `Customer`.`created`, `Customer`.`modified` FROM `customers` AS > `Customer` WHERE `Customer`.`id` = 1 > > SELECT `OrderPart`.`id`, `OrderPart`.`order_id`, > `OrderPart`.`item_id`, `OrderPart`.`quantity` FROM `order_parts` AS > `OrderPart` WHERE `OrderPart`.`order_id` IN (1, 2) > > SELECT `Item`.`name` FROM `items` AS `Item` WHERE `Item`.`id` = 1 > > SELECT `Item`.`name` FROM `items` AS `Item` WHERE `Item`.`id` = 4 > > SELECT `Item`.`name` FROM `items` AS `Item` WHERE `Item`.`id` = 5 > (...) > > Now I want to search the following fields: > 'Customer.name', > 'Customer.company', > 'Item.name' > But I want to have all entries where the one search term matches ANY > of the 3 fields. So normally I'd write this: > > $all_orders = $this->Order->find('all', array( > 'conditions' => array( > 'OR' => array( > 'Customer.name LIKE' => $searchterm, > 'Customer.company LIKE' => $searchterm, > 'Item.name LIKE' => $searchterm > ) > ), > 'contain' => array( > 'Customer', > 'OrderPart' => 'Item.name' > ) > )); > > But then I get an error (normal, because the Item table is not > joined): > SQL Error: 1054: Unknown column 'Item.name' in 'where clause' > > So how can I achive what I want to have?
You could use a join. See: http://book.cakephp.org/view/1047/Joining-tables http://nuts-and-bolts-of-cakephp.com/tag/cakephp-force-join/ There should also be some other info online. Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] 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
