= More on Criteria =

The '''Criteria''' class is Propel's suggestion for simplifying SQL queries.  Using a '''Criteria''' object -- and the implicitly created, logically inter-related '''Criterion''' objects, you can construct queries of significant complexity without writing SQL.  We do still recommend that you use SQL when it proves to be simpler, though.

== Criteria Operators ==

The `Criteria::add()` method accepts an additional `$operator` parameter. Most of the common SQL conditions can be formulated using one of these operators. For example, here is how to find all authors with first name "Karl" but last name _not_ "Marx"

{{{
#!php
<?php
$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
}}}
... results in SQL query like:
{{{
#!sql
SELECT * FROM author WHERE author.FIRST_NAME = 'Karl' AND author.LAST_NAME <> 'Marx';
}}}

Here is how to find all authors with last name "Tolstoy", "Dostoevsky", or "Bakhtin":

{{{
#!php
<?php
$c = new Criteria();
$c->add(AuthorPeer::LAST_NAME, array("Tolstoy", "Dostoevsky", "Bakhtin"), Criteria::IN);
$authors = AuthorPeer::doSelect($c);
}}}
... results in SQL query like:
{{{
#!sql
SELECT * FROM author WHERE author.LAST_NAME IN ('Tolstoy', 'Dostoevsky', 'Bakhtin');
}}}


Here is a list of the official operators, defined as constants in the `Criteria` class:
 * `Criteria::EQUAL` (default, '=' in SQL)
 * `Criteria::NOT_EQUAL` ('<>')
 * `Criteria::GREATER_THAN` ('>')
 * `Criteria::LESS_THAN` ('<')
 * `Criteria::GREATER_EQUAL` ('>=')
 * `Criteria::LESS_EQUAL` ('<=')
 * `Criteria::LIKE` ('LIKE')
 * `Criteria::NOT_LIKE` ('NOT LIKE')
 * `Criteria::IN` ('IN')
 * `Criteria::CUSTOM` (to create custom conditions, see below)
 * `Criteria::CUSTOM_EQUAL` (used in BasePeer::doUpdate() method, see below)

=== NULLs and Equality ===

There are `Criteria::ISNULL` and `Criteria::ISNOTNULL` operators; however, it is just as easy (and probably more intuitive) to use the default equality operator:

{{{
#!php
<?php

// this:
$c = new Criteria();
$c->add(BookPeer::PUBLISHER_ID, null, Criteria::NOT_EQUAL);

// ... is equivalent to:
$c = new Criteria();
$c->add(BookPeer::PUBLISHER_ID, null, Criteria::ISNOTNULL);

// ... and, similarly for equality checking, this:
$c = new Criteria();
$c->add(BookPeer::PUBLISHER_ID, null);

// ... is equivalent to:
$c = new Criteria();
$c->add(BookPeer::PUBLISHER_ID, null);

}}}

=== Criteria::CUSTOM ===

The Criteria::CUSTOM operator allows you to write your own condition.  In fact the column name is not used, though you still must specify it since it is used by Propel to create the clause map.

{{{
#!php
<?php
$con = Propel::getConnection(ReviewPeer::DATABASE_NAME);

$c = new Criteria();
$c->add(ReviewPeer::REVIEW_DATE, 'to_date('.ReviewPeer::REVIEW_DATE.', \'YYYY-MM-DD\') = '.$con->quote($date->format('Y-m-d'), Criteria::CUSTOM);
}}}

=== Criteria::CUSTOM_EQUAL ===

The Criteria::CUSTOM_EQUAL operator allows you to write your own condition for UPDATE queries when you need to use a specific value for a field set.

{{{
#!sql
UPDATE some SET my_first_field = my_first_field + 1, my_secund_field = CONCAT(my_first_field, 'suffix') WHERE my_third_field = 3
}}}

{{{
#!php
<?php

// Strip table reference before field name
$firstField = substr(SomePeer::MY_FIRST_FIELD, strrpos(SomePeer::MY_FIRST_FIELD, '.') + 1);

// Build WHERE criteria
$selc = new Criteria(SomePeer::DATABASE_NAME);
$selc->add(SomePeer::MY_THIRD_FIELD, 3);

// Build updated field criteria
$updc = new Criteria(SomePeer::DATABASE_NAME);
$updc->add(SomePeer::MY_FIRST_FIELD, array('raw' => $firstField . ' + ?', 'value' => 100), Criteria::CUSTOM_EQUAL);

BasePeer::doUpdate($selc, $updc, $con);
}}}

=== Your Own Operators ===

You can also specify your own operators as strings instead of using the built-in constants.  Note that doing so, however, may make your application less portable.

{{{
#!php
<?php

$c = new Criteria();
$c->add(BookPeer::TITLE, 'War\s+and', '~*'); // case-insensitive regex in PostgreSQL
}}}

== Limit and Offset ==

You can limit the number of results by setting the limit and the offset in Criteria:

{{{
#!php
<?php
$c = new Criteria();
$c->setLimit(10);
$c->setOffset(30);
$authors = AuthorPeer::doSelect($c);
}}}

This results in SQL query like:
{{{
#!sql
SELECT * FROM author LIMIT 10 OFFSET 30;
}}}

Naturally, the resulting SQL query depends on the database model used.

Tip: Propel offers a `PropelPager` class for automating paginated lists of resuls. 

== Ordering Results ==

Use `Criteria::addAscendingOrderByColumn()` or `Criteria::addDescendingOrderByColumn()` to define an order in a query:

{{{
#!php
<?php
$c = new Criteria();
$c->addAscendingOrderByColumn(AuthorPeer::LAST_NAME);
$authors = AuthorPeer::doSelect($c);
}}}

This results in SQL query like:
{{{
#!sql
SELECT * FROM author ORDER BY author.LAST_NAME ASC;
}}}

== Grouping Results ==

Use `Criteria::addGroupByColumn()` to add a GROUIP BY statement to your SQL query.

== Logically Complex Criteria ==

When you need to express logical relationships (AND, OR, etc.) between different criteria clauses, you need to manually combine individual ''Criterion'' objects together.  The ''Criterion'' objects are the components that are transparently assembled into the ''Criteria'' object when you use the ''Criteria->add()'' method.

'''Example 1:''' Find all authors with first name "Leo" OR last name of "Tolstoy", "Dostoevsky", or "Bakhtin"

{{{
#!php
<?php

$c = new Criteria();
$cton1 = $c->getNewCriterion(AuthorPeer::FIRST_NAME, "Leo");
$cton2 = $c->getNewCriterion(AuthorPeer::LAST_NAME,  array("Tolstoy", "Dostoevsky", "Bakhtin"), Criteria::IN);
 
// combine them
$cton1->addOr($cton2);
 
// add to Criteria
$c->add($cton1);
}}}

... results in SQL query like:
{{{
#!sql
SELECT ... FROM author WHERE (author.FIRST_NAME = 'Leo' OR author.LAST_NAME IN ('Tolstoy', 'Dostoevsky', 'Bakhtin'));
}}}

There are also some ''Criteria'' shortcuts if you want to perform a query with logical relationships between clauses ''that reference the same column''.

'''Example 2:''' Find all authors with first name 'Leo' or 'Karl'

Using verbose ''Criterion'' approach, this looks like:
{{{
#!php
<?php

$c = new Criteria();
$cton1 = $c->getNewCriterion(AuthorPeer::FIRST_NAME, "Leo");
$cton2 = $c->getNewCriterion(AuthorPeer::FIRST_NAME, "Karl");
 
// combine them
$cton1->addOr($cton2);
 
// add to Criteria
$c->add($cton1);
}}}

Using shorthand ''Criteria'' methods:
{{{
#!php
<?php

$c = new Criteria();
$c->add(AuthorPeer::FIRST_NAME, "Leo");
$c->addOr(AuthorPeer::FIRST_NAME, "Karl");
}}}

It is important to note that these shorthand ''Criteria'' methods have a number of limitations -- primarily that they only work for single column relationships.  From the author's perspective, it is not adviseable to use these methods when you need to express logical relationships between clause since they obscure the actual relationsihps between the ''Criterion'' objects and can easily lead to difficult-to-debug query generation issues.  (These methods will probably change or go away in Propel 2.)

== Case Sensitivity ==

If you specify that a Criteria is case-insensitive, then special considerations will be made for matching and sorting (order by) on text columns.  ''The specifics depend greatly on which RDBMS you are using.''

A query constructed with the following Criteria:
{{{
#!php
<?php

$c = new Criteria();
$c->setIgnoreCase(true);
$c->add(BookPeer::TITLE, 'war%', Criteria::LIKE);
$c->addAscendingOrderByColumn(BookPeer::TITLE);
}}}

... will result in something like this in PostgreSQL:

{{{
#!sql
SELECT .... FROM book WHERE book.TITLE ILIKE 'war%' ORDER BY UPPER(book.TITLE)
}}}

''Note that the operator used is {{{ILIKE}}} because in PostgreSQL LIKE is not case-sensitive like it is in MySQL.''

== Joins ==

You can add joins in a Criteria using the '''`addJoin(left-col, right-col[, join-type])`''' method.  For example:

{{{
#!php
<?php
$c = new Criteria();
$c->addJoin(ReviewPeer::BOOK_ID, BookPeer::ID, Criteria::INNER_JOIN);
$c->add(ReviewPeer::REVIEWER, $someVar);
}}}
{{{
#!sql
// SQL result
SELECT ... 
FROM review 
INNER JOIN book ON review.BOOK_ID = book.ID 
WHERE review.REVIEWER = '$somevar'
}}}

If you omit the join type, the join translates to an implicit join. While this has the same effect as INNER JOIN it is represented differently at the SQL level:

{{{
#!php
<?php
$c = new Criteria();
$c->addJoin(ReviewPeer::BOOK_ID, BookPeer::ID);
$c->add(ReviewPeer::REVIEWER, $someVar);
}}}
{{{
#!sql
// SQL result
SELECT ... 
FROM review, book 
WHERE review.BOOK_ID = book.ID 
  AND review.REVIEWER = '$somevar'
}}}

Supported JOIN types include:
 * `null` (implicit join)
 * `Criteria::LEFT_JOIN`
 * `Criteria::RIGHT_JOIN`
 * `Criteria::INNER_JOIN`

Criteria also supports multiple join conditions with the `addMultipleJoin()` method. For example, to create a join between the ReaderFavorite table and the BookOpinion table on both BOOK_ID and READER_ID, use the following syntax:

{{{
#!php
<?php
$c = new Criteria();
$c->addMultipleJoin(array(
    array(ReaderFavoritePeer::BOOK_ID, BookOpinionPeer::BOOK_ID),
    array(ReaderFavoritePeer::READER_ID, BookOpinionPeer::READER_ID))
  Criteria::INNER_JOIN);
}}}
{{{
#!sql
// SQL result
SELECT ...
FROM reader_favorite 
INNER JOIN book_opinion ON (reader_favorite.BOOK_ID = book_opinion.BOOK_ID 
                        AND reader_favorite.READER_ID = book_opinion.READER_ID)
}}}

You can add a third operator to each join condition to allow complex joins:

{{{
#!php
<?php
$c = new Criteria();
$c->addMultipleJoin(array(
    array(Book::USER_ID, UserPeer::ID))
    array(UserPeer::RANK, 12, Criteria::GREATER_THAN),
  Criteria::LEFT_JOIN);
}}}
{{{
#!sql
// SQL result
SELECT ...
FROM book
LEFT JOIN user ON (book.USER_ID = user.ID AND user.RANK > 12)
}}}

== Customizing Select Columns ==

If you wish to use Criteria to execute a query that will ''not'' be used to hydrate a Propel result set, you can customize the select columns.  ''(Note that Propel requires complete result sets in order to populate objects, so custom select columns should only be used for queries that are not used to populate objects.)''

{{{
#!php
<?php
$m_criteria = new Criteria();
$m_criteria->clearSelectColumns()->addSelectColumn(YourProjectnamePeer::COL_NAME);
$stmt = BasePeer::doSelectStmt($m_criteria);
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
  echo $row[0];
}
}}}