Development

ApplyingCustomJoinsInDoSelect

You must first sign up to be able to contribute.

Version 3 (modified by tamcy, 12 years ago)
fix typo (again)

Applying custom joins in doSelect* method

You want to join a table when running doSelect method to save you from doing multiple queries. If for various reasons you cannot refer it to a foreign key column, you can still write your own doSelectJoin* method like the code generated by Propel. This page describes how to achieve this.

Say you want to join Article with Book then join with Category, with article.book_id = book.id AND book.category_id = category.id.

First open ArticlePeer.php and add the following method (In fact the code is taken mostly from the propel generated base classes):

  public static function doSelectJoinBookAndCategory(Criteria $c, $con = null)
  {
    $c = clone $c;

    // Set the correct dbName if it has not been overridden
    if ($c->getDbName() == Propel::getDefaultDB()) {
        $c->setDbName(self::DATABASE_NAME);
    }

    // Add select columns for Article
    ArticlePeer::addSelectColumns($c);
    $startcol2 = (ArticlePeer::NUM_COLUMNS - ArticlePeer::NUM_LAZY_LOAD_COLUMNS) + 1;

    // Add select columns for Book
    BookPeer::addSelectColumns($c);
    $startcol3 = $startcol + BookPeer::NUM_COLUMNS ;

    // Add select columns for Category
    CategoryPeer::addSelectColumns($c);

    // [NOTE 1]
    $c->addJoin(ArticlePeer::BOOK_ID, BookPeer::ID);
    $c->addJoin(BookPeer::CATEGORY_ID, CategoryPeer::ID);

    $rs = BasePeer::doSelect($c, $con);
    $results = array();

    while($rs->next())
    {
      // Hydrate the Article object
      $omClass = ArticlePeer::getOMClass();

      $cls = Propel::import($omClass);
      $obj1 = new $cls();
      $obj1->hydrate($rs);

      // Hydrate the Book object
      $omClass = BookPeer::getOMClass();

      $cls = Propel::import($omClass);
      $obj2 = new $cls();
      $obj2->hydrate($rs, $startcol2);

      // Hydrate the Category object
      $omClass = CategoryPeer::getOMClass();

      $cls = Propel::import($omClass);
      $obj3 = new $cls();
      $obj3->hydrate($rs, $startcol3);

      // [NOTE 2]
      $obj1->setBook($obj2);
      $obj1->setCategory($obj3); 
      $results[] = $obj1;
    }
		
    return $results;
  }

Notes:

1. In case an Article may not have a Book but you still want it, apply left join instead:

		$c->addJoin(ArticlePeer::BOOK_ID, BookPeer::ID, Criteria::LEFT_JOIN);
		$c->addJoin(BookPeer::CATEGORY_ID, CategoryPeer::ID, Criteria::LEFT_JOIN);

2. If you had ever looked into the generated code of the base class, you will see it loops the previously fetched results for the same object by comparing the primary keys to prevent multiple instances of the same record. I don't care much about single instance so I assign the Book and Category object to Article without checking.

Since setBook() and setCategory() doesn't exist, you have to write it yourself.

Open Article.php and add the following fields and methods:

  protected 
    $book = null,
    $category = null;

  public function setBook($book)
  {
    $this->book = $book;
  }

  public function setCategory($category)
  {
    $this->category = $category;
  }

  public function getBook()
  {
    return $this->book;
  }

  public function getCategory()
  {
    return $this->category;
  }

Or if you want a smarter get*() :

  public function getBook()
  {
    if (!$this->book)
	$this->book = BookPeer::retrieveByPk($this->getBookId());
    return $this->book;
  }

  public function getCategory()
  {
    if (!$this->category)
    {
	$book = $this->getBook();
	if ($book instanceof Book)
	  $this->category = CategoryPeer::retrieveByPk($book->getCategoryId());
    }
    return $this->category;
  }
?>

That's it! You can use ArticlePeer::doSelectJoinBookAndCategory() to fetch the books also with the book and category fields. Calling $article->getBook() and $article->getCategory() won't cause an extra query. To use this function in a pager, call $pager->setPeerMethod('doSelectJoinBookAndCategory');. Note that you can use the ordinal doCount for the above example. However if you are doing something that affects the result count (like grouping, or joinging that causes some rows not fetched) in the doSelect* method, you will have to roll your own doCount* method and call $pager->setPeerMethod('doCountJoinBookAndCategory'); to ensure proper count.