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

Thanks for your response.

Unfortunately, that did not work. I'm still only getting the last element of the "OR" array. FWIW: Cake 1.3.10

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 

Any other help? 

On Tuesday, July 31, 2012 4:09:16 PM UTC-4, lavin wrote:

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