SUBSCRIBE VIA RSS


Subscribe to our feed

Symfony Experts

Symfony Experts
If you have an urgent question for a symfony-related issue, this is the place to ask.

Topics

Stack Overflow


The old fashioned way

RECENT TUNES

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 by in  Web Development   |  

17 Responses to Propel Queries using Custom SQL, Peer Classes, and Criterion Objects

  1. smeves says:

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

  2. MaWoe says:

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

  3. Salim Qadri says:

    thanks, I used this for my work.

  4. hartym says:

    thanks, i use it as a memo site for thoose everyday propel hacks 🙂

  5. tommy says:

    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

  6. Scott Meves says:

    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);
    }
    
  7. smente says:

    Thank You.

  8. bharathkumar says:

    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

  9. Rowan says:

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

  10. Maksym says:

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

  11. Manel says:

    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.

  12. Kamil says:

    Thank you, really usefull tips.
    Cheers

  13. Phuc says:

    Hi,
    Thank you for this acticle. But i have a problem when use propel sql like that :
    $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);
    Result : Call to undefined method DebugPDO::createStatement()
    I use symfony 1.27 .
    And have error the same if i use prepareStatement();
    Could you help me?

  14. Scott Meves says:

    Hi Phuc, these functions only work with Symfony 1.0.

  15. Leonardo says:

    Hello!
    I’m new to symfony and I wanted to know how to make a custom criterion.
    That is, in the DB I have a field “name” and has 4 rows (Compañía, Compañia, Companía y Compania)
    I want to find the field “name” = “COMpanía” and that this, give me back the 4 rows (Compañía, Compañia, Companía y Compania)
    Thanks.

  16. Gordon Illan says:

    Would somebody please post the Propel code to accomplish the following:

    UPDATE pages SET page_order = CASE id WHEN 11 THEN 0 WHEN 12 THEN 1 WHEN 13 THEN 2 WHEN 14 THEN 3 END WHERE id IN (11,12,13,14)