sfPropelFinder plugin

The sfPropelFinder is a symfony plugin that provides an easy API for finding Propel objects - that is, easier than the Peer methods and the Criteria stuff.

Overview

The idea behind this plugin is to write queries to retrieve Propel objects, but fast. Think of sfPropelFinder as "jQuery for Propel". It also aims at putting the things in the right order, meaning that writing a find() query will feel natural for those familiar with SQL.

<?php
// With Peer and Criteria
$c = new Criteria()
$c->add(ArticlePeer::TITLE, '%world', Criteria::LIKE);
$c->add(ArticlePeer::IS_PUBLISHED, true);
$c->addAscendingOrderByColumn(ArticlePeer::CREATED_AT);
$articles = ArticlePeer::doSelect($c);

// with sfPropelFinder
$articles = sfPropelFinder::from('Article')->whereTitle('like', '%world')->whereIsPublished(true)->orderByCreatedAt()->find();

sfPropelFinder uses the same fluid interface as the sfFinder, so you won't be lost.

You can also implement your own business logic to encapsulate complex queries, so that your queries look like real language:

<?php
$finder = new ArticleFinder();
$articles = $finder->recent()->withComments()->notAnonymous()->wellRated()->find();

Installation

• Install the plugin

> php symfony plugin-install http://plugins.symfony-project.com/sfPropelFinderPlugin

• Clear the cache

> php symfony cc

Usage

Finding objects

<?php
// Finding all Articles
$articles = sfPropelFinder::from('Article')->find();
// Finding 3 Articles
$articles = sfPropelFinder::from('Article')->find(3);
// Finding a single Article
$article = sfPropelFinder::from('Article')->findOne();

Adding WHERE clause

<?php
$articleFinder = sfPropelFinder::from('Article');
// Finding all Articles where title = 'foo'
$articles = $articleFinder->where('Title', 'foo')->find();
// Finding all Articles where title like 'foo%'
$articles = $articleFinder->where('Title', 'like', 'foo%')->find();
// Finding all Articles where published_at less than time()
$articles = $articleFinder->where('PublishedAt', '<', time())->find();

// You can chain WHERE clauses
$articles = $articleFinder->
  where('Title', 'foo')->
  where('PublishedAt', '<', time())->
  find();
// Or even better, use the _and() and _or() methods for SQL-like code
$articles = $articleFinder->
  where('Title', 'foo')->
   _and('PublishedAt', '<', time())->
    _or('Title', 'like', 'bar%')->
  find();

// The where() method accepts simple or composed column names ('ClassName_ColumnName')
$articles = $articleFinder->where('Article_Title', 'foo')->find();
// You can also use the magic whereXXX() method, removing the column argument and concatenating it to the method name
$articles = $articleFinder->whereTitle('foo')->find();

Ordering results

<?php
$articleFinder = sfPropelFinder::from('Article');
// Finding all Articles ordered by created_at (ascending order by default)
$articles = $articleFinder->orderBy('CreatedAt')->find();
// Finding all Articles ordered by created_at desc
$articles = $articleFinder->orderBy('CreatedAt', 'desc')->find();
// You can also use the magic orderByXXX() method
$articles = $articleFinder->orderByCreatedAt()->find();

Combining methods

The methods of the sfPropelFinder object return the current finder object, so you can chain them together in a single call, and finish by a find() to launch the query.

<?php
// everything chained together
$articles = sfPropelFinder::from('Article')->where('Title', 'like', '%world')->_and('IsPublished', true)->orderBy('CreatedAt')->find();
// You can write it in several lines, too
$articles = sfPropelFinder::from('Article')->
  where('Title', 'like', '%world')->
   _and('IsPublished', true)->
  orderBy('CreatedAt')->
  find();

The syntax should remind you of sfFinder and sfTestBrowser.

Joins

<?php
$article1 = new Article();
$article1->setTitle('Hello, world!');
$article1->save();
$comment = new Comment();
$comment->setContent('You rock!');
$comment->setArticle($article1);
$comment->save();
// Add a join statement
// No need to tell the finder which columns to use for the join, just the related Class
// After all, the columns of the FK are already defined in the schema
$article = sfPropelFinder::from('Article')->
  join('Comment')->
  where('Comment_Content', 'You rock!')->
  findOne();
// You can chain joins if you want to make it more complex
$article2 = new Article();
$article2->setTitle('Hello again, world!');
$article2->save();
$author1 = new Author();
$author1->setName('John');
$author1->save();
$comment = new Comment();
$comment->setContent('You rock!');
$comment->setArticle($article2);
$comment->setAuthor($author1);
$comment->save();
$article = sfPropelFinder::from('Article')->
  join('Comment')->
  join('Author')->
  where('Author_Name', 'John')->
  findOne();
// You can also use the magic joinXXX() method

Minimizing queries

Even if you do a Join, Propel will issue new queries when you fetch related objects:

<?php
$comment = sfPropelFinder::from('Comment')->
  join('Article')->
  where('Article_Title', 'Hello, world')->
  findOne();
$article = $comment->getArticle();  // Needs another database query

Just as Propel offers generated doSelectJoinXXX() methods, sfPropelFinder alows you to hydrate related objects in a single query - you just have to call the with() method to specify which objects the main object should be hydrated with.

<?php
$comment = sfPropelFinder::from('Comment')->with('Article')->
  join('Article')->
  where('Article_Title', 'Hello, world')->
  findOne();
$article = $comment->getArticle();  // Same result, with no supplementary query

The power of the with() method is that it can guess relationships just as well as join(), and will add the call to join() if you didn't do it yourself. So you can do for instance:

<?php
$category1 = new Category();
$category1->setName('Category1');
$category1->save();
$article1 = new Article();
$article1->setTitle('Hello, world!');
$article1->setCategory($category1);
$article1->save();
$comment = new Comment();
$comment->setContent('foo');
$comment->setArticle($article1);
$comment->save();

$comments = sfPropelFinder::from('Comment')->
  with('Article', 'Category')->
  find();      // One single query here
foreach ($comments as $comment)
{
  echo $comment->getArticle()->getCategory()->getName();  // No query needed, the related Article and article Category are already hydrated
}

Counting objects

<?php
// Counting all Articles
$nbArticles = sfPropelFinder::from('Article')->count();

Deleting objects

<?php
// Deleting all Articles
$nbArticles = sfPropelFinder::from('Article')->delete();
// Deleting a selection of Articles
$nbArticles = sfPropelFinder::from('Article')->
  where('Title', 'like', 'foo%')->
  delete();

Writing your own business logic into a finder

You can create a new finder for your objects, with custom methods. The only prerequisites are to extend sfPropelFinder and to define a protected $peerClass property. Then, the object has access to a protected $criteria property, which is a Propel Criteria that can be augmented in the usual way. Don't forget to return the current object ($this) in the new methods.

For instance, to add a recent() method to an article finder:

<?php
class ArticleFinder extends sfPropelFinder
{
  protected $peerClass = 'ArticlePeer';

  public function recent()
  {
    $this->criteria->add(
      ArticlePeer::CREATED_AT,
      time() - sfConfig::get('app_recent_days', 5) * 24 * 60 * 60,
      Criteria::GREATER_THAN
    );
    return $this;
  }
}

Hacking the finder

If the finder doesn't (yet) provide the method to build the query you need, you can still call Criteria methods on the finder objects, and they will be applied to the finder's internal Criteria object.

<?php
$articles = sfPropelFinder::from('Article')->
  where('Title', 'like', 'foo%')->
  addOr(ArticlePeer::TITLE, 'bar%', Criteria::LIKE)-> // that's a Criteria method
  findOne();

If you're not sure about what query is issued by the finder, you can always check the SQL code before executing a termination method by calling getCriteria()->toString(), or after executing a termination method by calling the getLatestQuery() method.

<?php
$finder = sfPropelFinder::from('Article')->where('Title', 'foo');
echo $finder->getCriteria()->toString();
// SELECT FROM article WHERE article.TITLE=?
$finder->findOne();
echo $finder->getLatestQuery();
// 'SELECT article.ID, article.VERSION, article.TITLE, article.CATEGORY_ID FROM article WHERE article.TITLE=\'foo\' LIMIT 1'

TODO

• Put as a parent class in the PeerBuilder? so that every Peer class can be a finder
• Merge with sfPropelImpersonatorPlugin!
• Add support for with() in findPk() (and document the method)

Changelog

2008-04-17 | Trunk

• jug: Fixed problem in a particular join case
• francois: Added sfPropelFinder::with() method (based on sfPropelObjectPeerImpersonator::populateObjects() code by hartym)
• francois: Added support for magic andXXX() and orXXX() methods.
• jug: Fixed _and() and _or() so that they give expected results, rather than the buggy results of Propel's addAnd() and addOr()

2008-03-31 | 0.2.0 Beta

• francois: De-emphasized the use of magic methods in the unit tests and README
• francois: Added sfPropelFinder::_and() and sfPropelFinder::_or() methods
• francois: Added support for Criteria methods (no more limit to what you can do with a finder!)
• francois: Added sfPropelFinder::getLatestQuery() method
• francois: Added sfPropelFinder::delete() method
• francois: Added sfPropelFinder::joinXXX() method
• francois: Added sfPropelFinder::join() method
• francois: Added complete whereClassName_ColumnName() syntax
• francois: Added sfPropelFinder::count() method

2008-03-27 | 0.1.0 Alpha

• francois: Initial public release.

Active tickets

#3306

[sfPropelFinder] Added function set() (Fluid interface to BasePeer::doUpdate())

Check the source code