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?
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
