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

Woohoo! That was it. I just wasn't reading the docs close enough. All that nesting gets to be pretty hard to read.

Thanks so much for the help. It's greatly appreciated.

On Wednesday, August 1, 2012 4:12:18 PM UTC-4, lavin wrote:

How about:

$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( 
                                        array('Equipment.location_id' => null), 
                                        array('Equipment.location_id' => -1 ), 
                                        array('Equipment.location_id' => -2) 
                                ) 
                        ) 
                ));  

According to an example in  http://book.cakephp.org/1.3/view/1030/Complex-Find-Conditions that sets multiple conditions on the same field inside an "OR":

 array(

    'OR' => array(

array('Post.title LIKE' => '%one%'),

array('Post.title LIKE' => '%two%')

    )

);

2012/8/1 scs

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

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