Setting complex ‘find’ conditions in CakePHP can be tricky. I picked an example of Cakephp complex find conditions from one of my CakePHP projects.
I wanted to list all auctions which are “not closed” and whose’ end date/time is greater that the current date/time. I can use the following statement.
$conditions = array('Auction.is_closed'=>0, 'Auction.end_datetime >= NOW()');However, here, there are more sql conditions to follow. For example, auctions need to be filtered by price range. Because auctions are of three kinds, A). Auction only, B) Auction & Buy and C) Buy Only auctions need to be filtered by type as well. If it’s a kind A or B the Price to be shown is from “current_bid” field otherwise it is from “price” field.
Okay. Time to write the actual AND condition (in the context of main conditions array) by extending the $conditions array given above.
$conditions["OR"] = array(
array(
"OR"=>array("Auction.type"=>array('auction', 'auctionbuy')),
"Auction.current_bid > "=>$this->request->query['value']
),
array(
"OR"=>array("Auction.type"=>array('buy', 'buyoffer')),
"Auction.price >"=>$this->request->query['value']
)
);One may notice that this particular condition is further divided into two OR conditions which again has two AND conditions, one each. Also, every first condition in the child’s AND element is again a set of OR conditions.
Assuming a value of 1000 in the $this->request->query[‘value’] for example, it will output the following sql statement:
WHERE `Auction`.`is_closed` = '0' AND `Auction`.`end_datetime` >= NOW() AND ((((`Auction`.`type` IN ('auction', 'auctionbuy')) AND (`Auction`.`current_bid` > 1000))) OR (((`Auction`.`type` IN ('buy', 'buyoffer')) AND (`Auction`.`price` > 1000))))I hope this example of CakePHP complex find conditions helps someone.
