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

TWITTER

Stack Overflow


The old fashioned way

RECENT TUNES

  Propel Articles

July 21, 2009 – 5:06pm Propel criteria on custom columns (with addAsColumn)

This still trips me up. If you are using propel criteria objects and want to add a condition on a custom column, you have to do a bit of trickery to get it working just right. More…

Posted by in  Uncategorized   |     |  4 Comments »

March 14, 2009 – 2:46pm Symfony Propel Enum types

Propel does not support enum column types in its schemas, since the enum type is not support across all database types. There are a few ways around this. More…

Posted by in  Uncategorized   |     |  4 Comments »

January 23, 2009 – 4:42pm Uploading a file with Symfony 1.2

There is a post hidden within the symfony documentation that discusses how to upload a file using the new forms framework within symfony 1.2. If you are curious about the best way to handle file uploads, it’s worth reading this What’s New in Symfony 1.2 post first, and then come back here for a quick summary of how this works.

Let’s say you would like to allow users to upload PDF files to your site. You store data related to these file uploads in an “article” table in your database. This table has a column named “file” that stores the file name of the uploaded pdf.

The symfony forms framework will generate the basic (and not so basic) code to get you started. If you haven’t yet generated your form classes yet, you can do so with the command:

More…

February 29, 2008 – 1:45pm Propel Transactions with Symfony

It’s easy to create transactions with Propel. This can be very useful when you are deleting rows from your database from multiple tables, and want to do an “all or nothing” approach so that things don’t get messy. More…

January 24, 2008 – 4:48pm Propel Criteria Left Join: using addJoin() and addAlias() to join a table twice

Here is a way to do left join on two columns in a table that both were foreign keys in the same table using Propel. This might happen if for example you have a table with a parent_id and a child_id in a nested set that both refer to the same table, or as an example you have a table “documentation” and each documentation can be categorized into a main category and a sub category.

What happens when you want to do a left join in your documentation query, so that if present both the related category and sub_category objects get included in the results?

$c->addJoin(DocumentationPeer::CATEGORY, CategoryPeer::ID, Criteria::LEFT_JOIN);
$c->addJoin(DocumentationPeer::SUB_CATEGORY, CategoryPeer::alias('c2', CategoryPeer::ID), Criteria::LEFT_JOIN);
$c->addAlias('c2', CategoryPeer::TABLE_NAME);

Done and done!

January 14, 2008 – 2:55pm symfony propel-load-data

Loading data from fixtures can be extremely useful to pre-populate your database so you have something to test and code with. I frequently use the propel-load-data task in two ways, the first when altering the schema, and the second to add some new data on top of whats in the database.

This will rebuild your db and model from schema.yml, insert the new structure into your db, and load data from the fixtures in /data/fixtures:

./symfony propel-build-all-load frontend

To load in additional data from a specific fixture file, you can use this command:

./symfony propel-load-data frontend dev data/fixtures/[filename].yml append
Posted by in  Web Development   |     |  3 Comments »

December 13, 2007 – 12:54am Propel Set Distinct: setDistinct()

Propel’s use of the Criteria class has many unknown features documented deep within the propel API. One of my favorites is setDistinct(), which will add the DISTINCT keyword into the start of your query. This is very useful when you want to create a select menu that presents a list of pre-existing values for a column in your database. More…

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.

May 12, 2007 – 5:29pm Left Joins with multiple conditions using Propel Criteria

Symfony by default uses Propel for its ORM. Although Propel supports supports custom SQL I have learned how to take advantage of the built-in Criteria class to help construct queries. As described in the Propel trac,

…its database neutrality and logical simplicity make it a good choice for expressing many common queries; however, for a very complex query, it may prove more effective (and less painful) to simply use a custom SQL query to hydrate your Propel objects. (propel trac)

In building a complex search system for a client, I benefited greatly from being able to add and remove columns and conditions to a criteria object on the fly without having to worry about the actual SQL syntax. However, eventually I hit a wall when I needed to add a left join using multiple columns. Adding left joins to a criteria object is typically done like this:

$c = new Criteria();
$c->addJoin(TABLE_A::ID, TABLE_B::ID, Criteria::LEFT_JOIN);
/* equivalent to: "...left join TABLE_B ON (TABLE_A.ID=TABLE_B.ID)" */

However, this syntax does not support a join with multiple conditions, so you can’t produce something like this:

...left join TABLE_B ON (TABLE_A.ID=TABLE_B.ID AND TABLE_B.VALUE > 100)

In playing around quite a bit and examining the source code, I realized you could hack the addJoin method call and trick it into adding the extra column for you. The addJoin method actually is creating a new Join object that has a left and right column definition as defined in the method call. These are used to build the join condition:

$condition = $join->getLeftColumn() . '=' . $join->getRightColumn();

(see the source)

So, if you add the extra columns to the RightColumn like so:

$c->addJoin(TABLE_A::ID, TABLE_B::ID.' AND '.TABLE_B::VALUE.' > 0', Criteria::LEFT_JOIN);

the generated SQL will yield something like this:

...LEFT JOIN TABLE_B ON (TABLE_A.ID = TABLE_B.ID AND TABLE_B.VALUE > 0)

Although it’s a hack and it may not work under all conditions, so far it has been a suitable workaround.