SUBSCRIBE VIA RSS


Subscribe to our feed

Status Updates

  • First iPhone app nearly complete! I've been waiting for 3 weeks for Apple to finish "conducting company identity verification."
    3 days ago
  • Rainy day! Staying indoors. Creating computerized players in a simulation game that models cooperation in the work place.
    75 days ago
  • Up at 8AM, with a hot cup of coffee brought back from Cafe Lola in Ann Arbor. Will I succeed in putting in 8 billable hours in one day?
    101 days ago
  • full scale irish band outside my window playing the pipes for the past 20 minutes. pretty fun.
    109 days ago
  • listening to Kristin on Maxim Radio!
    115 days ago
  • back from an amazing meal at ouest with even more amazing company.
    116 days ago
  • wondering how so many online tshirt companies stay in business--ones that sell shirts with funny messages, i never see them in real life
    122 days ago

Topics

TWITTER

The old fashioned way

RECENT TUNES

« Safari 3 Beta out for OS X / Windows - Crashes Unexpectedly Symfony exceptions, logging, and debugging »

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.

Posted in  Web Development   |     |  delicious  Digg

12 Responses to “Propel Queries using Custom SQL, Peer Classes, and Criterion Objects”

  1. apologies for the code formatting. at some point we’ll clean it up.

    By smeves on Jun 12, 2007

  2. Good help. I didn’t know how to create custom queries. Yet I’m missing an elegant solution for doing a fulltext-search.

    By MaWoe on Oct 14, 2007

  3. Thanks for tips.

    By Bayarsaikhan on Oct 25, 2007

  4. thanks, I used this for my work.

    By Salim Qadri on Nov 2, 2007

  5. thanks, i use it as a memo site for thoose everyday propel hacks :)

    By hartym on Jan 3, 2008

  6. Thanks for this cool article, but how I am able to use the ‘cnt’ property in an object?

    $c->addAsColumn(’cnt’, ‘COUNT(’.FavoriteBookPeer::BOOK_ID.’)');

    addAsColumns doesn’t get hydrated so I have to do it on my own, right?

    Regards,
    tommy

    By tommy on Feb 29, 2008

  7. Tommy,
    I think you are right that you will have to hydrate the objects on your own. The FavoriteBook example added the ‘cnt’ column purely to help sort the results, but if you wanted to retrieve that column along side the book objects, you could do something like this:

    
$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');

$rs = BookPeer::doSelectRS($c);
$n = BookPeer::NUM_COLUMNS;
$results = array();
while($rs->next()) {
  $book = new Book();
  $book->hydrate($rs);
  $bookCount = $rs->getInt($n+1);
  // or you could do $bookCount = $rs->getString('cnt') but that break if you added conflicting column names
  $results[] = array($book, $bookCount);
}

    By Scott Meves on Feb 29, 2008

  8. Thank You.

    By smente on Jun 3, 2008

  9. SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
    FROM Persons
    INNER JOIN Orders
    ON Persons.P_Id=Orders.P_Id
    ORDER BY Persons.LastName;

    how to write this code in propel

    By bharathkumar on Nov 29, 2008

  10. bharathkumar, see the propel criteria generator: http://propel.jondh.me.uk/

    By Rowan on Dec 16, 2008

  11. Thank you!!!
    Method to compare to column in one table is great!
    It’s what I’m looking for

    By Maksym on May 12, 2009

  12. Thanks!!! You have saved my day!!!

    I need to order a query putting the null values of one field at the end.

    I’ve combined your tip with this: http://www.shawnolson.net/a/730/mysql-sort-order-with-null.html. I hope that this can help anyone.

    By Manel on Jun 5, 2009

Post a Comment

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word