Matthew Mar10

SQL HAVING versus WHERE / Propel 1.3 oddity

During some recent re-work of Symfony model code I stumbled over a gotcha that brought me back to basics. It’s easy to let the elegance of Propel rot your brain — here’s a bit of brain-rot combined with a Propel 1.3 bug I tripped over.

A dashboard view needs to query against a large list of content — content stored in a table with a long list of fields. Retrieving the entire table and hydrating over it isn’t the most efficient solution, so we only grab what’s needed:

// inside lib/model/SomePeer.php

$c = new Criteria( self::DATABASE_NAME );

$c->clearSelectColumns();
$c->addAsColumn( 'id', self::CONTENTID );
$c->addAsColumn( 'title', self::TITLE );
$c->addAsColumn( 'content', self::CONTENT );

// ...

/**
 * Generates something like:
 * SELECT content.CONTENTID AS `id`, content.TITLE AS `title`, content.content AS `content`
 *   FROM content;
 */

Simple so far, right?

Ok, but now the dashboard needs to display only a list of content which has been localized. We need to JOIN this table against the localizations table, but there’s a catch:

// inside lib/model/ContentPeer.php

$c = new Criteria( self::DATABASE_NAME );

$c->clearSelectColumns();
$c->addAsColumn( 'id', self::ID );
$c->addAsColumn( 'title', self::TITLE );
$c->addAsColumn( 'content', self::CONTENT );

// only get content which has been localized
$c->addJoin( self::ID, LocalizationsPeer::CONTENT_ID, Criteria::INNER_JOIN );

// WRONG, fails
$c->add(
  SltLocalizationsPeer::STATUS_ID,
  Array( "PENDING_REVIEW", "APPROVED" ),
  Criteria::IN
);

// ...

Doh! We get a nasty FAIL message from Propel:

Propel fails on JOIN using custom columns via Criteria::addAsColumn

Ok Propel, so you choked on my Criteria. Why? If you look at the generated query, you’ll notice Propel fails to output the left table, which causes the crash. Hmm, why would Propel leave out the first table? Well, if we go back to the basics we automagically work-around this Propel oddity with no problem…using Critera::addHaving() instead of add().

Here’s the fix:

// inside lib/model/ContentPeer.php

$c = new Criteria( self::DATABASE_NAME );

$c->clearSelectColumns();
$c->addAsColumn( 'id', self::ID );
$c->addAsColumn( 'title', self::TITLE );
$c->addAsColumn( 'content', self::CONTENT );
$c->addSelectColumn( LocalizationsPeer::STATUS_CODE );

// only get content which has been localized
$c->addJoin( self::ID, LocalizationsPeer::CONTENT_ID, Criteria::INNER_JOIN );

// works
$c->addHaving( $c->getNewCriterion(
  LocalizationsPeer::STATUS_CODE,
  Array( "PENDING_REVIEW", "APPROVED" ),
  Criteria::IN
));

// ...

/**
 * Generates something like:
 * SELECT content.CONTENTID AS `id`, content.TITLE AS `title`, content.content AS `content`, localizations.STATUS_CODE
 *   FROM content INNER JOIN localizations
 *     ON content.ID=localizations.CONTENT_ID
 * HAVING localizations.STATUS_CODE IN ( "PENDING_REVIEW", "APPROVED" );
 */

Note that the HAVING will fail unless LocalizationsPeer::STATUS_CODE is in the result set. Remember: if the value in your conditional clause is from a group of records, HAVING is your friend. Otherwise, WHERE is where it’s at. This is important if you’re doing some pivot-table style report.

Let’s say, for example, we want to check on the overall status of localizations for various languages. Using SQL aggregate functions, we can easily achieve this:

$c = new Criteria( self::DATABASE_NAME );

$c->clearSelectColumns();

$dateField = 'DATE( '.LocalizationsPeer::REQUESTED.' )';
$c->addAsColumn( 'Date', $dateField );

$conditions = Array(
  'English'  => LocalizationsPeer::TARGET_LANGUAGE.'="EN" ',
  'French'  => LocalizationsPeer::TARGET_LANGUAGE.'="FR" ',
  'Spanish' => LocalizationsPeer::TARGET_LANGUAGE.'="ES" ',
  ...
);

$conditionFormat = "SUM( IF( %s, 1, 0))";
foreach( $conditions as $as => $condition )
  $c->addAsColumn( $as, sprintf( $conditionFormat, $condition ));

$c->addJoin( self::ID, LocalizationsPeer::CONTENT_ID, Criteria::INNER_JOIN );
$c->addHaving( $c->getNewCriterion(
  LocalizationsPeer::STATUS_CODE,
  "AWAITING_LOCALIZATION"
));

$c->addGroupBy( $dateField );
$c->addAscendingOrderByColumn( $dateField );

// ...

/**
 * Generates something like:
 *   SELECT
 *         DATE( localizations.REQUESTED ) AS `Date`,
 *         SUM( IF( localizations.TARGET_LANGUAGE="EN", 1, 0 )) AS `English`,
 *         SUM( IF( localizations.TARGET_LANGUAGE="FR", 1, 0 )) AS `French`,
 *         SUM( IF( localizations.TARGET_LANGUAGE="ES", 1, 0 )) AS `Spanish`,
 *         ...
 *     FROM content INNER JOIN localizations
 *       ON content.ID=localizations.CONTENT_ID
 *   HAVING localizations.STATUS_CODE="AWAITING_LOCALIZATION"
 * GROUP BY DATE( localizations.REQUESTED )
 * ORDER BY DATE( localizations.REQUESTED ) ASC;
 */

Propel oddities can be head scratchers. Sometimes going back to the basics and asking yourself “does this make SQL sense?” is the sure fire way to troubleshoot your code.

&ldquote;An expert is a man who has made all the mistakes which can be made, in a narrow field.&rdquote; – Niels Bohr