Useful Propel criteria methods and constants
I’m working with criteria quite often lately and must say it is a handy way of query writing. The only problem I have with criteria is that I don’t seem to find a simple overview (list) of the most important methods you can add to it. I don’t really like the Propel website as I don’t find the thing I need in a few seconds and that is a must for a lot of people. If I create a list of the most important criteria methods for myself, I rather share it with you guys…
**Simple Select query **with 2 criteria to check:
$c = new Criteria(); $c->add(AuthorPeer::FIRST_NAME, "Karl"); $c->add(AuthorPeer::LAST_NAME, "Marx", Criteria::NOT_EQUAL); $authors = AuthorPeer::doSelect($c); // $authors contains array of Author objects
In SQL this will be:
SELECT ... FROM author WHERE author.FIRST_NAME = 'Karl' AND author.LAST_NAME <> 'Marx';
It’s quite simple to write the criteria, the only thing needed to write them is a list of options.
To add Or to the criteria that have to be checked write:
$cton1 = $c->getNewCriterion(AuthorPeer::FIRST_NAME, 'Karl'); $cton2 = $c->getNewCriterion(AuthorPeer::LAST_NAME, 'Marx', Criteria:: NOT_EQUAL); // combine them $cton1->addOr($cton2);
In SQL this will be:
SELECT ... FROM author WHERE author.FIRST_NAME = 'Karl' OR author.LAST_NAME <> 'Marx';
Quite simple isn’t it, and the usage of criteria is quite readable once you start knowing all the options.
Possible operators for criteria (write them like Criteria::Equals)
- EQUAL (default)
- NOT_EQUAL
- GREATER_THAN
- LESS_THAN
- GREATER_EQUAL
- LESS_EQUAL
- LIKE
- NOT_LIKE
- IN
- CUSTOM
This allows you to write your own condition as second parameter.
- CUSTOM_EQUAL
This is used to write a custom condition in an UPDATE query
- ISNULL
- ISNOTNULL
For some methods you need to add a line to the criteria to specify if they have to be used or not:
$c->setIgnoreCase(true);
This is to specify if the query should be case sensitive or not.
$c->addJoin(ReviewPeer::BOOK_ID, BookPeer::ID, Criteria::INNER_JOIN);
INNER JOIN (default) the table in parameter 1 to the table in parameter 2. In the same way you can RIGHT or LEFT JOIN.
$c->addAscendingOrderByColumn(table::column);
This will add an ascending order for the specified column. Off course you can to the opposite too by using addDescendingOrderByColumn.
$criteria->addSelectColumn(self::LABEL);
This will add only this column to the select statement, by default he selects all fields of a table (default *)
All info about the methods and constants of criteria can be found here.
Comments