June 12, 2007 – 12:06pm Propel Queries using Custom SQL, Peer Classes, and Criterion Objects

Propel provides many ways to generate queries. Here are a few of the more common methods.

Perhaps the easiest way for newcomers to Propel are custom queries written using standard SQL.

$con = Propel::getConnection(DATABASE_NAME);
$sql = "SELECT books.* FROM books
    WHERE NOT EXISTS (SELECT id FROM review WHERE book_id = book.id)";
$stmt = $con->createStatement();
$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);
$books = BookPeer::populateObjects($rs);

Another method would be to use the peer class to gain some more flexibility in case down the line you decide to change your column names. For this we will use the PrepareStatement() method. This example also returns specific data from the query rather than entire propel objects.

public function getTotals($banner_id = NULL, $letter = NULL) {
	$con = Propel::getConnection();

	$sql = "SELECT SUM(".LetterBannerPeer::VIEWS.") AS view_total,
		SUM(".LetterBannerPeer::CLICKS.") AS click_total
		FROM ".LetterBannerPeer::TABLE_NAME."
		WHERE ".LetterBannerPeer::LETTER." = ?
		AND ".LetterBannerPeer::REGION_ID." = ?
		GROUP BY (".LetterBannerPeer::LETTER.")";

	$stmt = $con->PrepareStatement($sql);
	$stmt->setString(1, $letter);
	$stmt->setInt(2, REGION_ID);

	$rs = $stmt->executeQuery();
	while ($rs->next()) {
		$totals['views'] = $rs->getInt('view_total');
		$totals['clicks'] = $rs->getInt('click_total');
	}
	return $totals;
}

Notice how anytime you want to insert actual values into the query, you put in a ? (question mark) as a place holder which is then automatically escaped for the query by calling the $stmt->setString($position, $value) function.

For our third example we will use the Criterion Object. This function returns a propel object.

public static function getResponseTypes($message_type_id)
{
	$c = new Criteria();
	$c->addJoin(MessageTypePeer::ID,
		MessageTypeResponsePeer::RESPONSE_TYPE_ID, Criteria::LEFT_JOIN);
	// think of a Criterion object as one 'XX=YY' within the WHERE clause
	$cton1 = $c->getNewCriterion(
		MessageTypeResponsePeer::MESSAGE_TYPE_ID, $message_type_id
	);
	$cton2 = $c->getNewCriterion(
		MessageTypePeer::NAME, 'general'
	);
	$cton1->addOr($cton2);  // combine them into one (XX=YY OR AA=BB) clause
	$c->add($cton1); 		// add to Criteria

	return MessageTypePeer::doSelect($c);
}

The Criteria object provides some other useful functions for generating queries. This is a great example of how to use addAsColumn(), addGroupByColumn(), and addDescendingOrderByColumn().

$c = new Criteria();
$c->addJoin(BookPeer::ID, FavoriteBookPeer::BOOK_ID, Criteria::LEFT_JOIN);
$c->addAsColumn('cnt', 'COUNT('.FavoriteBookPeer::BOOK_ID.')');
$c->addGroupByColumn(BookPeer::ID);
$c->addDescendingOrderByColumn('cnt');
$book = BookPeer::doSelect($c);

If you want to use a criteria object but only select certain columns, you can do something like this:

$c = new Criteria();
$c->clearSelectColumns()
$c->addSelectColumn(self::PAYABLE_TO);
$c->add(self::ID, $id);

$rs = self::doSelectRS($c);
$payable_to = false;
if ($rs->next()) {
  $payable_to = $rs->getInt(1);
}

If you want to do a subselect, you can use Criteria::CUSTOM.

$c = new Criteria();
$subSelect = "users.user_type_id IN (
    SELECT
          user_type.ID
    FROM
          user_type
    WHERE
         user_type.name = 'public'
    )";
$c->add(UserPeer::USER_TYPE_ID, $subSelect, Criteria::CUSTOM);
$publicUsers = UserPeer::doSelect($c);

You of course could rewrite that query using JOINS if you thought that was too messy.

This custom criterion type is also useful when you want to compare two fields from the same table.

$c = new Criteria();
$c->add(MyTablePeer::COL1, MyTablePeer::COL1.'>='.MyTablePeer::COL2, Criteria::CUSTOM);

Take a look at snippets 75 and 53 for more information.