Re: find with complex conditions ("AND" and "OR")

Try something like this:

$params = array('contain' => 'Product.name',
'fields' => array('DISTINCT(Equipment.product_id) AS
id', 'Product.name'),
'order' => array('Product.name'),
'group' => array('Equipment.product_id'),
'conditions' => array(
'AND' => array(
'Equipment.company_id' => $this->passedArgs['company'],
'Equipment.deleted' => 0,
'OR' => array(
'Equipment.location_id' => null,
'Equipment.location_id' => -1,
'Equipment.location_id' => -2
)
)
));

Allowing the "OR" subset to be one of the elements considered inside the AND

2012/7/31 scs <jsfink92@gmail.com>:
> I'm trying to perform what has been called a complex condition in some of
> the documentation I have found. I want to use "AND" and "OR" in the
> conditions portion of my query via find('all', $params). I haven't been able
> to find anything that seems to match my issue (although I'm sure it's out
> there).
>
> I'm not sure what to do. I've tried many different variations of the
> conditions array, none seem to work. At best, the query is built with only 1
> of the "OR" items, always the last one in the array.
>
> Here's the information:
> $params = array('contain' => 'Product.name',
> 'fields' => array('DISTINCT(Equipment.product_id) AS id',
> 'Product.name'),
> 'order' => array('Product.name'),
> 'group' => array('Equipment.product_id'),
> 'conditions' => array('AND' => array('Equipment.company_id'
> => $this->passedArgs['company'], 'Equipment.deleted' => 0,),
> 'OR' => array('Equipment.location_id'
> => null, 'Equipment.location_id' => -1, 'Equipment.location_id' => -2))
> );
> $equipList = $this->Equipment->find('all', $params);
>
> The sql debugged is: SELECT DISTINCT(`Equipment`.`product_id`) AS id,
> `Product`.`name`, `Product`.`id` FROM `equipment` AS `Equipment` LEFT JOIN
> `products` AS `Product` ON (`Equipment`.`product_id` = `Product`.`id`) WHERE
> ((`Equipment`.`company_id` = 23) AND (`Equipment`.`deleted` = 0)) AND
> `Equipment`.`location_id` = -2 GROUP BY `Equipment`.`product_id` ORDER BY
> `Product`.`name` ASC
>
> What I am looking for is: SELECT DISTINCT(`Equipment`.`product_id`) AS id,
> `Product`.`name`, `Product`.`id` FROM `equipment` AS `Equipment` LEFT JOIN
> `products` AS `Product` ON (`Equipment`.`product_id` = `Product`.`id`) WHERE
> ((`Equipment`.`company_id` = 23) AND (`Equipment`.`deleted` = 0)) AND
> (`Equipment`.`location_id` IS NULL OR `Equipment`.`location_id` = -1 OR
> `Equipment`.`location_id` = -2) GROUP BY `Equipment`.`product_id` ORDER BY
> `Product`.`name` ASC
>
> Any and all help is greatly appreciated.
> Thanks.
>
> --
> Our newest site for the community: CakePHP Video Tutorials
> http://tv.cakephp.org
> Check out the new CakePHP Questions site http://ask.cakephp.org and help
> others with their CakePHP related questions.
>
>
> To unsubscribe from this group, send email to
> cake-php+unsubscribe@googlegroups.com For more options, visit this group at
> http://groups.google.com/group/cake-php



--
-Carlos

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.


To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php