Re: find with complex conditions ("AND" and "OR")
You may have already realized but:
The AND array key is not necessary.
If you define the same key twice in php - you overwrite the same value
I.e.
debug(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;
Has nothing to OR - there's only one thing in that array.
>From the docs "By default, CakePHP joins multiple conditions with boolean
AND;" Therefore you don't actually need this:
'AND' => array( <-
So your final conditions should look like this:
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)
)
)
OR
array(
'Equipment.company_id' => $this->passedArgs['company'],
'Equipment.deleted' => 0,
'OR' => array(
array('Equipment.location_id' => null),
array('Equipment.location_id' => array(-1, -2) )
)
)
AD
--
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+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
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
Re: find with complex conditions ("AND" and "OR")
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`
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+unsubscr...@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+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
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 :
> 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+unsubscr...@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+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
find with complex conditions ("AND" and "OR")
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+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
