As many of you know, if you want to get something from database via Symfony you should build a query with help of Criteria() object.

Criteria class is a magic part of Propel and it works pretty well with easy queries.

But with custom joins, select distinct, complex criterion, subqueries, “custom group by” queries, it’s not so simple.

In this article, I will show you a collection of snippets, tools, links and suggestions about Criteria building.

As always, I wish you “a good read” and I ask you to send me bits of code and suggestions..

I will add them in this article.

Best Regards.
Mauro Casula
Symfony-Framework.com

CRITERIA CLASS API

PROPEL CRITERIA BUILDER

PROPEL CHEAT-SHEET

( from http://andreiabohner.wordpress.com )

SNIPPETS

( from symfony-project.org and forums )

HOW TO BUILD A SUB QUERY

Snippet
@author: Patrice Blanchardie

$c = new Criteria();

$c->addAsColumn(’group_name’, ‘(SELECT group.group_name FROM group WHERE group.id=group_id)’);

$c->addAscendingOrderByColumn($c->getColumnForAs(’group_name’));

$this->users = UserPeer::doSelect($c);

HOW TO BUILD A CUSTOM ORDER BY QUERY

Snippet
@author: Eric Delord

$c = new Criteria();
//Add order by column name with a custom Order for a given Field
$c->addAscendingOrderByColumn( sprintf(”FIELD(%s,%s)”, CustomPeer::ID, “10,11,12,13″) );

HOW TO BUILD A BETWEEN TWO DATES

Snippet
@author: Francois Zaninotto

$c = new Criteria();
$criterion = $c->getNewCriterion(FooPeer::CREATED_AT , date(’Y-m-d’, $from_date), Criteria::GREATER_EQUAL );
$criterion->addAnd($c->getNewCriterion(FooPeer::CREATED_AT , date(’Y-m-d’, $to_date), Criteria::LESS_EQUAL ));
$c->add($criterion);
$shows = FooPeer::doSelect($c);

HOW TO UPDATE SEVERAL FIELDS IN A ROW

Snippet
@author: Francois Zaninotto
// This snippet uses the base peer class method doUpdate as follows:
// BasePeer::doUpdate($select_criteria, $update_criteria, $connection);

$con = Propel::getConnection();

// select from…
$c1 = new Criteria();
$c1->add(FooPeer::ID, $foo_id);

// update set
$c2 = new Criteria();
$c2->add(FooPeer::PROPERTY, 5);

BasePeer::doUpdate($c1, $c2, $con);

HOW TO COMPARE 2 FIELDS FROM THE SAME RECORD

Snippet
@author: Francois Zaninotto

$c = new Criteria();
$c->add(FooPeer::COLUMN1, FooPeer::COLUMN1.’>=’.FooPeer::COLUMN2, Criteria::CUSTOM);

HOW TO GET DUPLICATED FIELDS

@author: http://www.symfonybr.com/

Example with a Cliente table and Email field.
This Criteria group by emails and count duplicated.

$c = new Criteria();
$c->clearSelectColumns();
$c->addSelectColumn(ClientePeer::EMAIL);
$c->addGroupByColumn(ClientePeer::EMAIL);
$c->addAsColumn(�numduplicados’, �COUNT(’.ClientePeer::EMAIL.�)’);
$c->addHaving($c->getNewCriterion(ClientePeer::EMAIL, �COUNT(cliente.EMAIL)>1′, Criteria::CUSTOM));
$c->addDescendingOrderByColumn(�COUNT(cliente.EMAIL)’);
$rs = ClientePeer::doSelectRS($c);

while ($rs->next()) {

$duplicados[$rs->get(1)] = $rs->get(2);
}

HOW TO RANDOMIZE CRITERIA RESULTS

Snippet
@author: Romain Dorgueil

$c = new Criteria()
$result = FooPeer::doSelect($c);
The shuffle() function randomizes the order of the elements in the array.
shuffle($result);