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.