Development

Changeset 10785

You must first sign up to be able to contribute.

Changeset 10785

Show
Ignore:
Timestamp:
08/11/08 18:41:08 (3 months ago)
Author:
francois
Message:

sfPropelFinderPlugin sfPropelFinder::join() now defaults to an INNER JOIN instead of a WHERE statement (will facilitate compatibility with Doctrine)

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • plugins/sfPropelFinderPlugin/README

    r10780 r10785  
    178178    // No need to tell the finder which columns to use for the join, just the related Class 
    179179    // After all, the columns of the FK are already defined in the schema. 
     180    // Note that the default join() call results in a SQL INNER JOIN clause 
     181    // This is somewhat different from the Propel default, which issues a WHERE clause, but the result is the same 
    180182 
    181183    // If subsequent conditions use explicit column names, 
    …… 
    192194      findOne(); 
    193195 
    194     // Or if you want a special type of join (left, right, inner) 
    195     $article = DbFinder::from('Article')-> 
    196       innerJoin('Comment')-> 
     196    // Or if you want a special type of join (left, right) 
     197    $article = DbFinder::from('Article')-> 
     198      leftJoin('Comment')-> 
    197199      where('Comment.Content', 'You rock!')-> 
    198200      findOne(); 
    …… 
    200202    // Or both 
    201203    $article = DbFinder::from('Article')-> 
    202       innerJoin('Article.Id', 'Comment.ArticleId')-> 
     204      leftJoin('Article.Id', 'Comment.ArticleId')-> 
    203205      where('Comment.Content', 'You rock!')-> 
    204206      findOne(); 
    …… 
    286288Just 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. 
    287289 
    288     $comment = DbFinder::from('Comment')->with('Article')-> 
     290    $comment = DbFinder::from('Comment')-> 
     291      with('Article')-> 
    289292      join('Article')-> 
    290293      where('Article.Title', 'Hello, world')-> 
    …… 
    419422 
    420423### Updating objects 
    421  
    422424 
    423425    $article1 = new Article; 
    …… 
    533535### 2008-08-11 | Trunk 
    534536 
     537* francois: `sfPropelFinder::join()` now defaults to an `INNER JOIN` instead of a `WHERE` statement (will facilitate compatibility with Doctrine) 
    535538* francois: Implemented `sfDoctrine::combine()` 
    536539* francois: Implemented `sfDoctrineFinder::orWhere()` 

  • plugins/sfPropelFinderPlugin/lib/sfPropelFinder.php

    r10780 r10785  
    10991099      { 
    11001100        $this->addCondition('and', $c->getFullyQualifiedName(), $object->getByName($c->getRelatedName(), BasePeer::TYPE_COLNAME), Criteria::EQUAL); 
     1101        break; 
    11011102      } 
    11021103    } 
    …… 
    12201221   * Infers $column1, $column2 and $operator from $relatedClass and some optional arguments 
    12211222   * Uses the Propel column maps, based on the schema, to guess the related columns 
     1223   * Beware that the default JOIN operator is INNER JOIN, while Criteria defaults to WHERE 
    12221224   * Examples: 
    12231225   *   $articleFinder->join('Comment') 
    1224    *    => $c->addJoin(ArticlePeer::ID, CommentPeer::ARTICLE_ID) 
     1226   *    => $c->addJoin(ArticlePeer::ID, CommentPeer::ARTICLE_ID, Criteria::INNER_JOIN) 
    12251227   *   $articleFinder->join('Category', 'RIGHT JOIN') 
    12261228   *    => $c->addJoin(ArticlePeer::CATEGORY_ID, CategoryPeer::ID, Criteria::RIGHT_JOIN) 
    …… 
    12491251        list($column1, $column2) = $this->getRelation($relatedClass); 
    12501252        $this->addRelation(sfPropelFinderUtils::getPeerClassFromClass($relatedClass), $alias); 
    1251         $operator = isset($args[1]) ? $args[1] : null; 
     1253        $operator = isset($args[1]) ? $args[1] : Criteria::INNER_JOIN; 
    12521254        break; 
    12531255      case 3: 

  • plugins/sfPropelFinderPlugin/test/unit/sfPropelFinderRelationsTest.php

    r10655 r10785  
    7777$databaseManager->initialize(); 
    7878 
    79 $t = new lime_test(82, new lime_output_color()); 
     79$t = new lime_test(83, new lime_output_color()); 
    8080 
    8181$t->diag('findRelation()'); 
    …… 
    158158$joins = $finder->getCriteria()->getJoins(); 
    159159$join = array_pop($joins); 
    160 $t->is($join->getJoinType(), null, 'join() ends up in a simple join'); 
     160$t->is($join->getJoinType(), Criteria::INNER_JOIN, 'join() ends up in an inner join'); 
    161161$t->is($join->getLeftColumnName(), 'ID', 'join($table) guesses the left column name'); 
    162162$t->is($join->getLeftTableName(), 'article', 'join($table) guesses the left table name'); 
    …… 
    295295$article1->setCategory($category1); 
    296296$article1->save(); 
    297 $sql = 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, category.ID, category.NAME FROM article, category WHERE article.CATEGORY_ID=category.ID LIMIT 1'; 
     297$sql = 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, category.ID, category.NAME FROM article INNER JOIN category ON (article.CATEGORY_ID=category.ID) LIMIT 1'; 
    298298$finder = sfPropelFinder::from('Article')->join('Category')->with('Category'); 
    299299$article = $finder->findOne(); 
    …… 
    308308$t->is($article->getCategory()->getName(), 'cat1', 'fetching objects with a with() returns the correct related object'); 
    309309$t->is(Propel::getConnection()->getLastExecutedQuery(), $sql, 'with() called without a join() hydrates the related classes and avoids subsequent queries'); 
     310 
     311$finder = sfPropelFinder::from('Article')->leftJoin('Category')->with('Category'); 
     312$article = $finder->findOne(); 
     313$t->is($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, category.ID, category.NAME FROM article LEFT JOIN category ON (article.CATEGORY_ID=category.ID) LIMIT 1', 'calling a particular join() before with() changes the join clause'); 
    310314 
    311315ArticlePeer::doDeleteAll(); 
    …… 
    326330$finder = sfPropelFinder::from('Comment')->with('Article')->with('Author'); 
    327331$comment = $finder->findOne(); 
    328 $sql = 'SELECT comment.ID, comment.CONTENT, comment.ARTICLE_ID, comment.AUTHOR_ID, article.ID, article.TITLE, article.CATEGORY_ID, author.ID, author.NAME FROM comment, article, author WHERE comment.ARTICLE_ID=article.ID AND comment.AUTHOR_ID=author.ID LIMIT 1'; 
     332$sql = 'SELECT comment.ID, comment.CONTENT, comment.ARTICLE_ID, comment.AUTHOR_ID, article.ID, article.TITLE, article.CATEGORY_ID, author.ID, author.NAME FROM comment INNER JOIN article ON (comment.ARTICLE_ID=article.ID) INNER JOIN author ON (comment.AUTHOR_ID=author.ID) LIMIT 1'; 
    329333$t->is($finder->getLatestQuery(), $sql, 'you can call with() several times to hydrate more than one related object'); 
    330334$t->is($comment->getContent(), 'foo', 'you can call with() several times to hydrate more than one related object'); 
    …… 
    333337$t->is(Propel::getConnection()->getLastExecutedQuery(), $sql, 'with() called several tims hydrates the related classes and avoids subsequent queries'); 
    334338 
     339$sql = 'SELECT comment.ID, comment.CONTENT, comment.ARTICLE_ID, comment.AUTHOR_ID, article.ID, article.TITLE, article.CATEGORY_ID, category.ID, category.NAME FROM comment INNER JOIN article ON (comment.ARTICLE_ID=article.ID) INNER JOIN category ON (article.CATEGORY_ID=category.ID) LIMIT 1'; 
    335340$finder = sfPropelFinder::from('Comment')->with('Article')->with('Category'); 
    336341$comment = $finder->findOne(); 
    337 $t->is($finder->getLatestQuery(), 'SELECT comment.ID, comment.CONTENT, comment.ARTICLE_ID, comment.AUTHOR_ID, article.ID, article.TITLE, article.CATEGORY_ID, category.ID, category.NAME FROM comment, article, category WHERE comment.ARTICLE_ID=article.ID AND article.CATEGORY_ID=category.ID LIMIT 1', 'with() can even hydrate related objects via a related object'); 
     342$t->is($finder->getLatestQuery(), $sql, 'with() can even hydrate related objects via a related object'); 
    338343 
    339344$finder = sfPropelFinder::from('Comment')->with('Article', 'Category'); 
    340345$comment = $finder->findOne(); 
    341 $t->is($finder->getLatestQuery(), 'SELECT comment.ID, comment.CONTENT, comment.ARTICLE_ID, comment.AUTHOR_ID, article.ID, article.TITLE, article.CATEGORY_ID, category.ID, category.NAME FROM comment, article, category WHERE comment.ARTICLE_ID=article.ID AND article.CATEGORY_ID=category.ID LIMIT 1', 'with() accepts several arguments, so you don\'t need to call it several times'); 
     346$t->is($finder->getLatestQuery(), $sql, 'with() accepts several arguments, so you don\'t need to call it several times'); 
    342347 
    343348$t->diag('withI18n()'); 
    …… 
    353358$article1->save(); 
    354359 
     360$baseSQL = 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, article_i18n.CONTENT, article_i18n.ID, article_i18n.CULTURE FROM article INNER JOIN article_i18n ON (article.ID=article_i18n.ID) '; 
    355361sfContext::getInstance()->getUser()->setCulture('en'); 
    356362$finder = sfPropelFinder::from('Article')->withI18n(); 
    357363$article = $finder->findOne(); 
    358364$query = $finder->getLatestQuery(); 
    359 $t->is($query, 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, article_i18n.CONTENT, article_i18n.ID, article_i18n.CULTURE FROM article, article_i18n WHERE article_i18n.CULTURE=\'en\' AND article.ID=article_i18n.ID LIMIT 1', 'withI18n() hydrates the related I18n object with a culture taken from the user object'); 
     365$t->is($query, $baseSQL . 'WHERE article_i18n.CULTURE=\'en\' LIMIT 1', 'withI18n() hydrates the related I18n object with a culture taken from the user object'); 
    360366$t->is($article->getContent(), 'english content', 'withI18n() considers the current user culture for hydration'); 
    361367$t->is(Propel::getConnection()->getLastExecutedQuery(), $query, 'withI18n() hydrates the i18n object so that no further query is necessary'); 
    …… 
    365371$article = $finder->findOne(); 
    366372 
    367 $t->is($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, article_i18n.CONTENT, article_i18n.ID, article_i18n.CULTURE FROM article, article_i18n WHERE article_i18n.CULTURE=\'fr\' AND article.ID=article_i18n.ID LIMIT 1', 'withI18n() hydrates the related I18n object with a culture taken from the user object'); 
     373$t->is($finder->getLatestQuery(), $baseSQL . 'WHERE article_i18n.CULTURE=\'fr\' LIMIT 1', 'withI18n() hydrates the related I18n object with a culture taken from the user object'); 
    368374$t->is($article->getContent(), 'contenu français', 'withI18n() considers the current user culture for hydration'); 
    369375 
    …… 
    377383$finder = sfPropelFinder::from('Article')->with('I18n'); 
    378384$article = $finder->findOne(); 
    379 $t->is($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, article_i18n.CONTENT, article_i18n.ID, article_i18n.CULTURE FROM article, article_i18n WHERE article_i18n.CULTURE=\'en\' AND article.ID=article_i18n.ID LIMIT 1', 'with(\'I18n\') is a synonym for withI18n()'); 
     385$t->is($finder->getLatestQuery(), $baseSQL . 'WHERE article_i18n.CULTURE=\'en\' LIMIT 1', 'with(\'I18n\') is a synonym for withI18n()'); 
    380386$finder = sfPropelFinder::from('Article')->with('i18n'); 
    381387$article = $finder->findOne(); 
    382 $t->is($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, article_i18n.CONTENT, article_i18n.ID, article_i18n.CULTURE FROM article, article_i18n WHERE article_i18n.CULTURE=\'en\' AND article.ID=article_i18n.ID LIMIT 1', 'with(\'i18n\') is a synonym for withI18n()'); 
     388$t->is($finder->getLatestQuery(), $baseSQL . 'WHERE article_i18n.CULTURE=\'en\' LIMIT 1', 'with(\'i18n\') is a synonym for withI18n()'); 
    383389 
    384390$t->diag('withColumn()'); 
    …… 
    405411$comment = $finder->findOne(); 
    406412$t->is($comment->getColumn('Article.Title'), 'bbbbb', 'Additional columns added with withColumn() are stored in the object and can be retrieved with getColumn()'); 
    407 $t->is($finder->getLatestQuery(), 'SELECT comment.ID, comment.CONTENT, comment.ARTICLE_ID, comment.AUTHOR_ID, article.TITLE AS "Article.Title" FROM comment, article WHERE comment.ARTICLE_ID=article.ID LIMIT 1', 'Columns added with withColumn() can contain a dot (and are then escaped with double quotes in SQL)'); 
     413$t->is($finder->getLatestQuery(), 'SELECT comment.ID, comment.CONTENT, comment.ARTICLE_ID, comment.AUTHOR_ID, article.TITLE AS "Article.Title" FROM comment INNER JOIN article ON (comment.ARTICLE_ID=article.ID) LIMIT 1', 'Columns added with withColumn() can contain a dot (and are then escaped with double quotes in SQL)'); 
    408414 
    409415$comment = sfPropelFinder::from('Comment')-> 
    …… 
    465471  orderBy('NbComments'); 
    466472$article = $finder->findOne(); 
    467 $t->is($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, COUNT(comment.ID) AS NbComments FROM article, comment WHERE article.ID=comment.ARTICLE_ID GROUP BY article.ID ORDER BY NbComments ASC LIMIT 1', 'Columns added with withColumn() can be used for sorting'); 
     473$t->is($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, COUNT(comment.ID) AS NbComments FROM article INNER JOIN comment ON (article.ID=comment.ARTICLE_ID) GROUP BY article.ID ORDER BY NbComments ASC LIMIT 1', 'Columns added with withColumn() can be used for sorting'); 
    468474 
    469475$t->diag('sfPropelFinder::with() issues with object finders classes'); 

  • plugins/sfPropelFinderPlugin/test/unit/sfPropelFinderTest.php

    r10779 r10785  
    226226 
    227227$article = $finder->with('Category')->findPk($article2->getId()); 
    228 $t->cmp_ok(strpos($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, category.ID, category.NAME FROM article, category'), '===', 0, 'findPk() is compatible with with()'); 
     228$t->cmp_ok(strpos($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, category.ID, category.NAME FROM article INNER JOIN category'), '===', 0, 'findPk() is compatible with with()'); 
    229229 
    230230ArticlePeer::doDeleteAll(); 
    …… 
    523523$t->is( 
    524524  $finder->getLatestQuery(), 
    525   "SELECT $columns FROM article, category WHERE (category.NAME='foo' OR category.NAME='bar') AND article.CATEGORY_ID=category.ID", 
     525  "SELECT $columns FROM article INNER JOIN category ON (article.CATEGORY_ID=category.ID) WHERE (category.NAME='foo' OR category.NAME='bar')", 
    526526  'orWhere() works on a simple jointure' 
    527527); 
    …… 
    535535$t->is( 
    536536  $finder->getLatestQuery(), 
    537   "SELECT comment.ID, comment.CONTENT, comment.ARTICLE_ID, comment.AUTHOR_ID FROM comment, article, author WHERE (article.TITLE='foo' OR author.NAME='bar') AND comment.ARTICLE_ID=article.ID AND comment.AUTHOR_ID=author.ID", 
     537  "SELECT comment.ID, comment.CONTENT, comment.ARTICLE_ID, comment.AUTHOR_ID FROM comment INNER JOIN article ON (comment.ARTICLE_ID=article.ID) INNER JOIN author ON (comment.AUTHOR_ID=author.ID) WHERE (article.TITLE='foo' OR author.NAME='bar')", 
    538538  'orWhere() works on a multiple jointure' 
    539539); 
    …… 
    774774  withColumn('COUNT(comment.ID)', 'NbComments'); 
    775775$article = $finder->findOne(); 
    776 $t->is($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, COUNT(comment.ID) AS NbComments FROM article, comment WHERE article.ID=comment.ARTICLE_ID GROUP BY article.ID LIMIT 1', 'groupBy() accepts a column name and issues a GROUP BY clause'); 
     776$t->is($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, COUNT(comment.ID) AS NbComments FROM article INNER JOIN comment ON (article.ID=comment.ARTICLE_ID) GROUP BY article.ID LIMIT 1', 'groupBy() accepts a column name and issues a GROUP BY clause'); 
    777777 
    778778$finder = sfPropelFinder::from('Article')-> 
    …… 
    782782  withColumn('COUNT(comment.ID)', 'NbComments'); 
    783783$article = $finder->findOne(); 
    784 $t->is($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, article.ID AS foo, COUNT(comment.ID) AS NbComments FROM article, comment WHERE article.ID=comment.ARTICLE_ID GROUP BY foo LIMIT 1', 'groupBy() accepts a column alias and issues a GROUP BY clause'); 
     784$t->is($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, article.ID AS foo, COUNT(comment.ID) AS NbComments FROM article INNER JOIN comment ON (article.ID=comment.ARTICLE_ID) GROUP BY foo LIMIT 1', 'groupBy() accepts a column alias and issues a GROUP BY clause'); 
    785785 
    786786$finder = sfPropelFinder::from('Article')-> 
    …… 
    789789  withColumn('COUNT(comment.ID)', 'NbComments'); 
    790790$article = $finder->findOne(); 
    791 $t->is($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, COUNT(comment.ID) AS NbComments FROM article, comment WHERE article.ID=comment.ARTICLE_ID GROUP BY article.ID LIMIT 1', 'groupByXXX() accepts a column name and issues a GROUP BY clause'); 
     791$t->is($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, COUNT(comment.ID) AS NbComments FROM article INNER JOIN comment ON (article.ID=comment.ARTICLE_ID) GROUP BY article.ID LIMIT 1', 'groupByXXX() accepts a column name and issues a GROUP BY clause'); 
    792792 
    793793$t->diag('groupByClass()'); 
    …… 
    815815  withColumn('COUNT(comment.ID)', 'NbComments'); 
    816816$article = $finder->findOne(); 
    817 $t->is($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, COUNT(comment.ID) AS NbComments FROM article, comment WHERE article.ID=comment.ARTICLE_ID GROUP BY article.ID,article.TITLE,article.CATEGORY_ID LIMIT 1', 'groupByClass() accepts a model class name and issues a GROUP BY clause on all columns'); 
     817$t->is($finder->getLatestQuery(), 'SELECT article.ID, article.TITLE, article.CATEGORY_ID, COUNT(comment.ID) AS NbComments FROM article INNER JOIN comment ON (article.ID=comment.ARTICLE_ID) GROUP BY article.ID,article.TITLE,article.CATEGORY_ID LIMIT 1', 'groupByClass() accepts a model class name and issues a GROUP BY clause on all columns'); 
    818818 
    819819$t->diag('set()');