1. Legacy
    1. Summary of Problems with Criteria
  2. Requirements for New System
  3. The New Criteria
    1. Old vs. New Criteria Comparison
      1. Nested Logical Expressions
      2. Custom SQL
      3. Criteria Using SQL Functions
  4. Other Resources

Criteria 2.0

This document describes some design goals and implementation details for a new version of Criteria. This is a work in progress; see ticket:275 for a history of this code in the repository and some more usage examples & ideas.

Legacy

The Propel 1.x Criteria implementation comes from Torque's Criteria class (as close as was possible in PHP). The overall idea is very appealing -- especially to those who have had to use conditional statements to build SQL strings in the past. The actual implementation works fairly well for simple queries, but can very quickly get cumbersome & a bit inconsistent when doing more complex logic.

Summary of Problems with Criteria

  • Very poor support for using custom SQL in building criteria; essentially this comes from inability for Criteria to distinguish between custom SQL and column names
  • API breaks down when nesting logical statements -- required to use Criterion objects which come out of nowhere; the main problem is that Criteria "protects" user from some details for simple things but then provides no help when things get complex
  • Bloated with SQL methods that aren't necessarily complete (like JOIN vs. LEFT JOIN support, GROUP BY, etc.).
  • Used within Propel to store data that does is not criteria per se -- e.g. the !BasePeer::doUpdate() method takes a Criteria object as a simple column->value mapping for values to update.
  • The fact that Criteria uses table.COL_NAME values for the columns make it difficult to
    1. use custom table aliases in queries and
    2. apply runtime idnetifier quoting.

Requirements for New System

Here are some distilled requirements, based on the shortcomings mentioned above and general improvements we'd like to see in Propel 2.0:

  • The logical expression API must be completely consistent & intuitive.
  • We need abilty to support custom SQL expressions without a big headache.
  • We need to be able to determine & apply unique table aliases at query composition time.
  • We need to break out the JOIN, GROUP BY, etc. into a higher-level class, because a single Criteria object has a focus that is too narrow to address the concerns of things like JOIN (unique table aliasing, etc.).
  • We need to be able to have a system that makes it easy for us to apply runtime identifier quoting to the SQL.

The New Criteria

The first (and if it proves to be a good solution, perhaps the only) stab at addressing these new Criteria requirements is represented by ticket:275.

The new Criteria is inspired by the API of Hibernate's Criteria and (in my [hans] own recent experience) by the way that the IfTask handles logical conditions in Phing.

This solution adopts a few key strategies for addressing Criteria 1.0's shortcomings:

  • A new interface-driven design supports consistent nesting / logical relating behavior for Criteria and individual expression elements.
  • Expressions are (interface-implementing) objects in the new design.
  • The values of the peer constants will change from 'table.COL_NAME' to 'COL_NAME' (or maybe 'col_name'); this makes the binding between table and column more flexible, so we can apply differnet table aliases as needed.
  • Criteria objects are constructed for a specific (single) table and will be built by the peer method (e.g.) !AuthorPeer::createCriteria().
  • For SELECT queries, there is a new Query class which assumes some of the methods in Criteria that only apply to SELECT statements (and also which don't make sense in Criteria's freely nested context).

Standard expression classes (implement Expression) replace current Criteria::CONST values:

  • EqualsExpr
  • NotEqualsExpr
  • GreaterExpr
  • GreaterEqualsExpr
  • LessExpr
  • LessEqualsExpr
  • LikeExpr
  • NotLikeExpr
  • InExpr
  • NotInExpr

Support for custom SQL is provided by the LiteralSql class, which (thanks to interfaces!) can server both as a full Expression or as the value of one of the other Expression classes.

Some expression types contain other expressions (implement ContainerExpression):

  • AndExpr
  • OrExpr

Criteria also implements Expression, allowing Criteria (which may contain multiple nested expressiosn) to be logically related to each other the same way that expressions are.

Old vs. New Criteria Comparison

Here are some examples comparing the way Criteria 1.0 works to Criteria 2.0. Note: some of these examples won't actually work as written until the peer generators have been updated (e.g. for the new column name format).

Nested Logical Expressions

Criteria 1.0 

<?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);

Criteria 2.0 

<?php
$c = AuthorPeer::createCriteria();
$c->add(new OrExpr(new EqualExpr(AuthorPeer::FIRST_NAME, "Leo"), 
                   new InExpr(AuthorPeer::LAST_NAME,  array("Tolstoy", "Dostoevsky", "Bakhtin"))
                  )
       );

Custom SQL

Criteria 1.0 

<?php

$c = new Criteria();
$c->add(AuthorPeer::NAME, "char_length(name) = 4", Criteria::CUSTOM);

Note: the !AuthorPeer::NAME part of the above expression was only needed to conform to method signature, it was never actually used. That said, it was still a part of the Criteria object & could cause strange things to happen logically if it was used more than once.

Criteria 2.0 

<?php

$c = AuthorPeer::getCriteria();
$c->add(new LiteralSql("char_length(name) = 4"));

Criteria Using SQL Functions

This is a feature that was very poorly supported in Criteria 1.0. When it did work, it was only through hard-coding certain values (e.g. CURRENT_TIMESTAMP) in the SQL-building methods. For Criteria 2.0, we just reuse the SqlExpr class (used for custom SQL in previous example) to wrap values that should be treated as raw SQL.

Criteria 1.0 (Only support for a few SQL functions: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP) 

<?php

$c = new Criteria();
$c->add(BookReviewPeer::REVIEW_DATE, Criteria::CURRENT_DATE);

Criteria 2.0 

<?php
$c = BookReviewPeer::createCriteria();
$c->add(new EqualExpr(BookReviewPeer::REVIEW_DATE, new LiteralSql("current_date")));

// other examples also possible:
$c->add(new EqualExpr(BookReviewPeer::REVIEW_DATE, new LiteralSql("(select max(review_date) from review)")));

Other Resources

Others?