Developers

License

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

DbFinder plugin

The DbFinder is a symfony plugin that provides an easy API for finding Model objects, whether the underlying ORM is Propel or Doctrine. It can be seen as:

A usability layer to ease the use of Propel's Criteria object and Peer classes

An extension to Propel's limited capabilities to provide:

complex joins

custom hydration of related objects and columns

schema and relation introspection

A compatibility layer to allow plugins to work with Propel 1.2, Propel 1.3, and Doctrine

Overview

The idea behind this plugin is to write queries to retrieve model objects through an ORM, but fast. Inspired by Doctrine, Rails has_finder plugin and SQLAlchemy, DbFinder can be seen as "jQuery for symfony's model layer". 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.

DbFinder uses the same fluid interface as the sfFinder, so you won't be lost. It is compatible with symfony 1.0 to 1.2, with Propel 1.2 and 1.3, and with Doctrine 1.0. DbFinder comes with a Propel and a Doctrine adapter (sfPropelFinder, sfDoctrineFinder). Whenever you use DbFinder::from(), the finder will check whether you look for Propel or Doctrine objects and use the appropriate adapter.

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

Installation

Warning: The Doctrine implementation requires Doctrine 1.0. Unfortunately, only the version of sfDoctrinePlugin bundled for symfony 1.1 is compatible with Dbfinder, since the one for sf 1.0 uses the Doctrine 0.11 branch, and the one for sf 1.2 doesn't bundle the admin generator theme anymore.

Usage

Finding Objects

// Finding all Articles$articles = DbFinder::from('Article')->find();
// Finding 3 Articles$articles = DbFinder::from('Article')->find(3);
// Finding a single Article$article = DbFinder::from('Article')->findOne();
// Finding the last Article (the finder will figure out the column to use for sorting)$article = DbFinder::from('Article')->findLast();

Using Functions In A WHERE Clause

// When you need to apply a function, use whereCustom() instead of where()// Finding all Articles for a given month$articles = DbFinder::from('Article')->
whereCustom('date_format(Article.Title, \'%%y%%m\') = ?', $month)->
find();
// Notice that you must use the composed column name and the comparison operator// The finder will escape the $month parameter, so there is no risk of SQL injection// Finding all Articles where upper(title) = 'FOO' + 'BAR'$articles = DbFinder::from('Article')->
whereCustom('upper(Article.Title) = ? + ?', array('FOO', 'BAR'))->
find();
// When there is more than one parameter, use an array as second argument// Finding all Articles where PublishedAt is null AND upper(title) = 'FOO'$articles = DbFinder::from('Article')->
where('PublishedAt', 'is null')->
whereCustom('upper(Article.Title) = ?', 'FOO')->
find();
// whereCustom() adds a condition with 'AND'// Finding all Articles where PublishedAt is null OR upper(title) = 'FOO'$articles = DbFinder::from('Article')->
where('PublishedAt', 'is null')->
orWhereCustom('upper(Article.Title) = ?', 'FOO')->
find();
// orWhereCustom() adds a condition with 'OR'

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

Compare it to the code required to get these Comment objects without DbFinder, 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.

// Retrieving the last one of the related comments$comments = DbFinder::from($article->getComments())->
findLast();

Joins

// 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 = DbFinder::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.// Note that the default join() call results in a SQL INNER JOIN clause// This is somewhat different from the Propel default, which issues a WHERE clause, but the result is the same// 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 = DbFinder::from('Article')->
where('Comment.Content', 'You rock!')->
findOne();
// Using an explicit join is mostly useful if you want a special type of join// Use leftJoin(), and rightJoin() for this purpose// innerJoin() is a synonym for join()$article = DbFinder::from('Article')->
leftJoin('Comment')->
where('Comment.Content', 'You rock!')->
findOne();
// Using an explicit join can also help if you need to specify the members of the join$article = DbFinder::from('Article')->
leftJoin('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 = DbFinder::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 = DbFinder::from('Article')->
join('Comment')->
where('Author.Name', 'John')->
findOne();

Just as Propel offers generated doSelectJoinXXX() methods, DbFinder allows 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.

Note: Since the i18nTable and the is_culture schema properties are lost after Propel 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.

$article = DbFinder::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 = DbFinder::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 = DbFinder::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 = DbFinder::from('Article')->
join('Comment')->
withColumn('COUNT(Comment.Id)', 'NbComments')->
orderBy('NbComments')->
find();
$articles = DbFinder::from('Article')->
join('Comment')->
groupBy('Article.Id')->
withColumn('COUNT(Comment.Id)', '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 = DbFinder::from('Article')->
join('Category')->
withColumn('Category.CreatedAt', 'CategoryCreatedAt', 'Timestamp')->
findOne();
$categoryName = $article->getColumn('CategoryCreatedAt');

Deleting Objects

Updating Objects

$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
DbFinder::from('Article')->
where('Title', 'foo')->
set(array('Title' => 'updated title')); // 1// set() returns the number of modified columns
DbFinder::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
DbFinder::from('Article')->
set(array('Title' => 'updated title'), true);
// Beware that it may take a long time

Finding Objects From A Primary Key

$article = DbFinder::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 = DbFinder::from('Article')->
with('Category', 'I18n')->
findPk(123);
// Also works for objects with composite primary keys$articleI18n = DbFinder::from('ArticleI18n')->findPk(array(123, 'fr'));

Magic Finder Methods

The finder recognizes method calls composed of where/join/orderBy, and a class / column name. It then forwards the method call to the related method, adding the class/column name as a first argument.

DbFinder::from('Article')->whereTitle('foo')->find();
// same as
DbFinder::from('Article')->where('Title', 'foo')->find();
DbFinder::from('Article')->orderByCreatedAt()->find();
// same as
DbFinder::from('Article')->orderBy('CreatedAt')->find();
DbFinder::from('Article')->joinComment()->whereComment_Title('Hey')->findOne();
// same as
DbFinder::from('Article')->join('Comment')->where('Comment.Title', 'Hey')->findOne();

The most useful magic methods are findByXXX() and findOneByXXX().

DbFinder::from('Article')->findByTitle('foo');
// same as
DbFinder::from('Article')->where('Title', 'foo')->find();
DbFinder::from('Article')->findOneByTitle('foo');
// same as
DbFinder::from('Article')->where('Title', 'foo')->findOne();

Writing Your Own business Logic Into A Finder

You can create a new finder class for your models, with custom methods. The only prerequisites are to extend DbFinder, and to define a protected $class property.

For instance, you can create an child of DbFinder to retrieve Propel Article objects. This new finder has access to a protected query object by way of getQueryObject(). This object 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.

You can create several custom finders for a given model, in order to separate the finder methods into logical classes. Also, use the initialize() method to add finder conditions on all queries executed on a custom finder (think of sfAction::preExecute()).

Tip: Being able to use any class as a finder means that you can use the finder API to query model objects that are not backed by any ORM at all.

Applying filters

// filterBy() is an alternative to where(), which takes only two parameters: a key and a value// filterBy() converts the value and the guesses the comparison based on the column type$articleFinder = DbFinder::from('Article');
$articleFinder->filterBy('NbComments', '10');
// NbComments is an integer, so filterBy() translates into a simple where() and forces the value type to integer$articleFinder->where('NbComments', 10);
$articleFinder->filterBy('Title', '*foo*');
// Title is a string, and the value has wildcards, so filterBy() translates into a like comparison$articleFinder->where('Title', 'like', '%foo%');
$articleFinder->filterBy('CreatedAt', array('from' => 'yesterday', 'to' => 'today'));
// CreatedAt is a timestamp, and the value is an array, so filterBy() translates into a between comparison$articleFinder->where('CreatedAt', '<=', 'today')->where('CreatedAt', '>=', 'yesterday');
$articleFinder->filterBy('IsPublished', '1');
// IsPublished is a boolean, so filterBy() translates into a simple where() and forces the value type to boolean$articleFinder->where('IsPublished', true);
// filter() expects an associative array of key/value pairs$articleFinder->filter(array('NbComments' => '10',
'Title' => '*foo*',
'CreatedAt' => array('from' => 'yesterday', 'to' => 'today'),
'IsPublished' => '1'));
// same as$articleFinder->
filterBy('NbComments', 10)->
filterBy('Title', '%foo%')->
filterBy('CreatedAt', array('from' => 'yesterday', 'to' => 'today'))->
filterBy('IsPublished', 1);
// same as$articleFinder->
where('NbComments', 10)->
where('Title', 'like', '%foo%')->
where('CreatedAt', '<=', 'today')->
where('CreatedAt', '>=', 'yesterday')->
where('IsPublished', true);
// filter() uses filterByXXX() methods whenever they exist$articleFinder->filter(array('Title' => 'foo',
'Custom' => 'bar'));
// same as$articleFinder->
where('Title', 'like', '%foo%')->
filterByCustom('bar');
// filter() is therefore quite useful to filter a list based on an unsafe list of conditions,// like the query string found in the admin generator list view

Using Cache

// Using APC Cache with a lifetime of 10min = 600s$finder = DbFinder::from('Article a')->useCache(new sfProcessCache(), 600);
// Note that you can use any of the symfony cache classes, including the sf 1.1 ones// First time the finder executes a query, it stores the results in the cache$article = $finder->where('a.Title', 'foo')->findOne();
echo$article->getTitle(); // foo
...
// Next occurrences of the same query use the results from the cache, without any SQL query$article = $finder->where('a.Title', 'foo')->findOne(); // no SQL query executedecho$article->getTitle(); // foo// This also works with count() queries and complex find() queries using with() and withColumns()// Instead of cache object, pass `true` to useCache() to let DbFinder find a caching backend$finder = DbFinder::from('Article a')->useCache(true, 600);
// Activating cache for all read queries by defaultclass ArticleFinder extends DbFinder
{
protected $class = 'Article';
publicfunction initialize(){return$this->useCache(new sfProcessCache(), 600);
}}// Deactivating cache on a finder object$finder->useCache(false);

Warning: The query cache also works with the Doctrine adapter, although the cache object passed to useCache() must implement the Doctrine_Cache interface (which is not the case for the symfony cache classes). Also, count queries are not cached with the Doctrine adapter (see Ticket #1561).

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 sfPropelFinder objects, or call Doctrine_Query methods on the sfDoctrineFinder objects, and they will be applied to the finder's internal query object.

Tip: When developing with the finder, you may prefer to have an array or string representation of the results rather than an array of objects. The finder objects provides three methods (toArray(), toString() and toHtml()) that internally execute a find() and return something that you can output in your response.

Alternative Framework Components

DbFinder comes with a few handy classes to use with the symfony framework.

The plugin bundles a generator theme to build an administration module with DbFinder. It offers additional features and a great ease of use. Check the following example, and the attached doc/generator.txt documentation, for more details.

DbFinderPlugin also bundles special routing classes to take advantage of the object routing through DbFinder. Check the attached doc/routing.txt documentation, which explains how to enrich your routing rules with powerful routes looking like the following: