= 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
<?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::doSelectJoinCategory($c);

// with sfPropelFinder
$articles = sfPropelFinder::from('Article')->
  where('Title', 'like', '%world')->
  where('IsPublished', true)->
  orderBy('CreatedAt')->
  with('Category')->
  find();
}}}

`sfPropelFinder` uses the same fluid interface as the `sfFinder`, so you won't be lost. It is compatible with symfony 1.0 and 1.1, and with Propel 1.2 and 1.3.

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

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

== Installation ==

  * Install the plugin
  
    {{{
#!sh
> php symfony plugin-install http://plugins.symfony-project.com/sfPropelFinderPlugin
    }}}

  * Clear the cache

    {{{
#!sh
> php symfony cc
    }}}

== Usage ==

=== Finding objects ===

{{{
#!php
<?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();
// Finding the last Article (the finder will figure out the column to use for sorting)
$article = sfPropelFinder::from('Article')->findLast();
}}}

=== Adding WHERE clause ===

{{{
#!php
<?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();
// Or, when your search is on a single column, use the magic findByXXX() method
$articles = $articleFinder->findByTitle('foo');
}}}

=== Ordering results ===

{{{
#!php
<?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();
}}}

=== Chaining 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 any of the `find()` methods to launch the query.

{{{
#!php
<?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`.

=== Finding records related to another one ===

{{{
#!php
<?php
// Propel way
$comments = $article->getComments();
// sfPropelFinder way
$commentFinder = sfPropelFinder::from('Comment');
$comments = $commentFinder->
  where('ArticleId', $article->getId())->
  find();
// Or let the finder guess local and foreign columns based on the schema
$comments = $commentFinder->
  relatedTo($article)->
  find();
}}}

Since the finder way is longer than the native Propel way, what is the interest of using this `relatedTo()`? You get a `sfPropelFinder` object when you use `relatedTo()`, so it allows you to do things that the generated Propel getter don't allow:

{{{
#!php
<?php
// Retrieving the related comments, orderd by date
$comments = $commentFinder->
  relatedTo($article)->
  orderBy('CreatedAt', 'desc')->
  find();
// Retrieving the last one of the related comments
$comments = $commentFinder->
  relatedTo($article)->
  findLast();
}}}

Compare it to the code required to get these `Comment` objects without `sfPropelFinder`, and you will understand all the benefits the `relatedTo()` method provide.

Tip: Alternatively, a finder can be initialized from an array of Propel object. The resulting SQL query contains a 'IN ()' clause, so use this possibility with caution.

{{{
#!php
<?php
// Retrieving the last one of the related comments
$comments = sfPropelFinder::from($article->getComments())->
  findLast();
}}}

=== Joins ===

{{{
#!php
<?php
// Test data
$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
$article = sfPropelFinder::from('Article')->
  join('Comment')->
  where('Comment.Content', 'You rock!')->
  findOne();
// 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.

// If subsequent conditions use explicit column names,
// The finder can even guess the join table and you can omit the join() statement.
// This is the case here with Comment.Content, so the following also works
$article = sfPropelFinder::from('Article')->
  where('Comment.Content', 'You rock!')->
  findOne();

// So join() is mostly useful if you need to specify the members of the join
$article = sfPropelFinder::from('Article')->
  join('Article.Id', 'Comment.ArticleId')->
  where('Comment.Content', 'You rock!')->
  findOne();

// Or if you want a special type of join (left, right, inner)
$article = sfPropelFinder::from('Article')->
  innerJoin('Comment')->
  where('Comment.Content', 'You rock!')->
  findOne();

// Or both
$article = sfPropelFinder::from('Article')->
  innerJoin('Article.Id', 'Comment.ArticleId')->
  where('Comment.Content', 'You rock!')->
  findOne();

// You can chain joins if you want to make more complex queries
$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();
// In this example, Author.Name allows the finder to guess the last join
// So you can omit it
$article = sfPropelFinder::from('Article')->
  join('Comment')->
  where('Author.Name', 'John')->
  findOne();

// You can also use the magic joinXXX() method
$article = sfPropelFinder::from('Article')->
  joinComment()->
  where('Author.Name', 'John')->
  findOne();
}}}

=== Complex logic ===

{{{
#!php
<?php
// _and() and _or() only allow simple logical operations on a single condition
// For more complex logic, you have to use combine()
// It expects an array of named conditions to be combined, and an operator
// Use the fourth argument of where() to name a condition
$article = sfPropelFinder::from('Article')->
  where('Title', '=', 'Foo', 'cond1')->     // creates a condition named 'cond1'
  where('Title', '=', 'Bar', 'cond2')->     // creates a condition named 'cond2'
  combine(array('cond1', 'cond2'), 'or')->  // combine 'cond1' and 'cond2' with a logical OR
  findOne();
// SELECT article.* FROM article WHERE (article.TITLE = 'foo' OR article.TITLE = 'bar');

// combine accepts more than two conditions at a time
$articles = sfPropelFinder::from('Article')->
  where('Title', '=', 'Foo', 'cond1')->
  where('Title', '=', 'Bar', 'cond2')->
  where('Title', '=', 'FooBar', 'cond3')->
  combine(array('cond1', 'cond2', 'cond3'), 'or')->
  find();
// SELECT article.* FROM article WHERE (article.TITLE = 'foo' OR article.TITLE = 'bar') OR article.TITLE = 'FooBar';

// combine() itself can return a named condition to be combined later
// So it allows for any level of logical complexity
$articles = sfPropelFinder::from('Article')->
  where('Title', '=', 'Foo', 'cond1')->
  where('Title', '=', 'Bar', 'cond2')->
  combine(array('cond1', 'cond2'), 'or', 'TitleFooBar')->
  where('PublishedAt', '<=', $end, 'cond3')->
  where('PublishedAt', '>=', $begin, 'cond4')->
  combine(array('cond2', 'cond3'), 'and', 'PublishedInBounds')->
  combine(array('TitleFooBar', 'PublishedInBounds'), 'or')->
  find();
// SELECT article.* FROM article WHERE (
//  (article.TITLE = 'foo' OR article.TITLE = 'bar')
//  OR
//  (article.PUBLISHED_AT <= $end AND article.PUBLISHED_AT >= $begin)
// );
}}}

=== Minimizing queries ===

Even if you do a Join, Propel will issue new queries when you fetch related objects:
{{{
#!php
<?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
<?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
<?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
}
}}}

The `with()` method can also hydrate the related I18n objects, thus providing an equivalent to symfony's `doSelectWithI18n()` methods.
{{{
#!php
<?php
// Consider the following schema
//article:
//  title:       varchar(255)
//article_i18n:
//  content:     varchar(255)
$article = new Article();
$article->setTitle('Foo Bar');
$article->setCulture('en');
$article->setContent('english content');
$article->setCulture('fr');
$article->setContent('contenu français');
$article->save();

sfContext::getInstance()->getUser()->setCulture('en');
$article = sfPropelFinder::from('Article')->with('I18n')->findOne();
echo $article->getContent();   // english content
sfContext::getInstance()->getUser()->setCulture('fr');
$article = sfPropelFinder::from('Article')->with('I18n')->findOne();
echo $article->getContent();   // contenu français
}}}

Note: Since the `i18nTable` and the `ìs_culture` schema properties are lost after model generation, `with('I18n')` only works if the i18n table is named after the main table (e.g. 'Article' => 'ArticleI18n') and if the culture column name is `culture`. This is the default symfony behavior, so it should work if you didn't define special i18n table and column names.

=== Adding columns ===

If what you need is a single property of a related object, you probably don't need to hydrate the whole related object. For those cases, the finder allows you to add only one column of a related object with `withColumn()`. You can retrieve supplementary columns added by the finder by calling `getColumn()` on the resulting objects.

Warning: The `withColumn()` feature requires symfony's Behavior system. It will only work if you enable behaviors in `propel.ini` and rebuild your model afterwards.

{{{
#!php
<?php
$article = sfPropelFinder::from('Article')->
  join('Category')->
  withColumn('Category.Name')->
  findOne();
$categoryName = $article->getColumn('Category.Name');  // No supplementary query

// Beware that in this case, the related `Category` object is not hydrated, since `with()` was not used.
// That means that retrieving the related `Category` object will issue a new database query,
// so use `withColumn()` only when you need one or two supplementary columns instead of the whole object.
$categoryName = $article->getCategory()->getName();  // One supplementary query

// Just like with(), withColumn() adds an internal join if you don't do it yourself
$article = sfPropelFinder::from('Article')->
  withColumn('Category.Name')->
  findOne();
$categoryName = $article->getColumn('Category.Name');  // Works without a call to `join('Category')`

// withColumn() can use a column alias as second argument.
$article = sfPropelFinder::from('Article')->
  join('Category')->
  withColumn('Category.Name', 'category')->
  findOne();
$categoryName = $article->getColumn('category');

// This is particularly useful if you want to reuse a calculated column for sorting or grouping
$articles = sfPropelFinder::from('Article')->
  join('Comment')->
  withColumn('COUNT(comment.ID)', 'NbComments')->
  orderBy('NbComments')->
  find();

// Lastly, the supplementary columns added with withColumn() are considered string by default
// But you can force another data type by providing a third argument
$article = sfPropelFinder::from('Article')->
  join('Category')->
  withColumn('Category.CreatedAt', 'CategoryCreatedAt', 'Timestamp')->
  findOne();
$categoryName = $article->getColumn('CategoryCreatedAt');
}}}

=== Counting objects ===

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

=== Getting a paginated list of results ===

{{{
#!php
<?php
// Getting an initialized sfPropelPager object
$pager = sfPropelFinder::from('Article')->paginate($currentPage = 1, $maxResultsPerPage = 10);
// You can use the pager object as usual
printf("Showing results %d to %d on %d\n",
  $pager->getfirstIndice(),
  $pager->getLastIndice(),
  $pager->getNbResults());
foreach($pager->getResuts() as $article)
{
  echo $article->getTitle();
}
}}}

=== Deleting objects ===

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

=== Updating objects ===

{{{
#!php
<?php
$article1 = new Article;
$article1->setTitle('foo');
$article1->save();
$article2 = new Article;
$article2->setTitle('bar');
$article2->save();

// set() issues an UPDATE ... SET query based on an associative array column => value
sfPropelFinder::from('Article')->
  where('Title', 'foo')->
  set(array('Title' => 'updated title')); // 1

// set() returns the number of modified columns
sfPropelFinder::from('Article')->
  where('Title', 'updated title')->
  count(); // 1

// Beware that set() updates all records found in a signle row
// And bypasses any behavior registered on the save() hooks
// You can force a one-by-one update by setting the second parameter to true
sfPropelFinder::from('Article')->
  set(array('Title' => 'updated title'), true);
// Beware that it may take a long time
}}}

=== 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.

{{{
#!php
<?php
// For instance, add a `recent()` method to an article finder
class ArticleFinder extends sfPropelFinder
{
  protected $peerClass = 'ArticlePeer';

  public function recent()
  {
    return $this->where('CreatedAt', '>=', time() - sfConfig::get('app_recent_days', 5) * 24 * 60 * 60);
  }
}
// You can now use your custom finder and its methods together with the usual ones
$articleFinder = new ArticleFinder();
$articles = $articleFinder->
  recent()->
  orderByTitle()->
  find();
}}}

=== Finding Objects From A Primary Key ===

{{{
#!php
<?php
$article = sfPropelFinder::from('Article')->findPk(123);
// is equivalent to
$article = ArticlePeer::retrieveByPk(123);

// But it's longer to write so what's the point?
// You can hydrate related objects by using with()
// So you need a single query to retrieve an object and its related objects
$article = sfPropelFinder::from('Article')->
  with('Category', 'I18n')->
  findPk(123);

// Also works for objects with composite primary keys
$articleI18n = sfPropelFinder::from('ArticleI18n')->findPk(array(123, 'fr'));
}}}

=== 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
<?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
<?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 / Ideas ==

 * Allow i18n hydration of related objects (#3897)
 * Handle self-referencing relationships (e.g. parent_id), especially in with()
 * Handle multiple references to the same table (c.f. getFooRelatedByBarId())
 * Put as a parent class in the PeerBuilder so that every Peer class can be a finder
 * Merge with sfPropelImpersonatorPlugin!
 * Add a `__toString()` method which returns a var_export() of the results, or a description of the conditions if not yet executed
 * Implement iterator interface? That way, the query is only executed upon a foreach or an array access... And the finder can be seen as a collection
 * Column finder, which provides an easy interface to Creole (and PDO) for retrieval of columns instead of objects?

== Changelog ==

=== 2008-07-07 | 0.3.0 Beta ===

 * francois: Added `sfPropelFinder::combine()` method to handle complex queries with And and Or
 * francois: Added support for `with()` in `findPk()` (and documented the method)
 * francois: Added the ability to do left, right, and inner joins in a simple way
 * francois: Made `join()` useless if there is an explicit `where()` on the table afterwards
 * francois: Added a `prove.php` test file to launch all tests at once in a test harness
 * francois: Moved utility methods as static methods of a third-party class to take some weight off the main class
 * francois: Preferring `ClassName.ColumnName` over `ClassName.ColumnName` for complete column names
 * francois: Added Propel 1.3 compatibility
 * francois: Added `sfPropelFinder::set()` method (based on a patch by jug)
 * francois: Added `sfPropelFinder::withI18n()` method
 * francois: Added `sfPropelFinderPager` class and `sfPropelFinder::paginate()` method
 * francois: Added `sfPropelFinder::groupBy()` method
 * francois: `sfPropelFinder::from()` now accepts an array of Propel objects
 * francois: Added `sfPropelFinder::findByXXX()` and `sfPropelFinder::findOneByXXX()` methods
 * francois: Added `sfPropelFinder::relatedTo()` method
 * francois: Added `sfPropelFinder::findFirst()` and `sfPropelFinder::findLast()` methods
 * francois: Added `sfPropelFinder::withColumn()` method
 * 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.