Returning records from 3 tables via a Join Table with Propel in Symfony

Question

I have 3 database tables:

• article
• article_has_tag (2 FK's to the other tables)
• tag

I currently show a list of articles with the article's tags shown underneath but the number of queries grows as the list gets longer.

I want to loop over all the articles and get the tag objects from each one in turn.

Can it be done in 1 propel query?

Answer 1

Unless I'm misunderstanding your question, don't loop over anything as you'll generate bloat of a different kind.

Do a single query where "article" is joined to "article_has_tag" is joined to "tag". The single query should return the specified articles and tag names for the tags they have.

I use Doctrine myself so can't help you with the exact query but Googling brings up stuff like this: http://www.tech-recipes.com/rx/2924/symfony_propel_how_to_left_join/.

Also, the symfony definitive guide (which was written for Propel) should be able to help you.

Answer 2

I assume you are using Propel 1.3 or 1.4, and not yet Propel 1.5 (which is still in beta), as the latter has a very natural support for these multiple joins (inspired, in part, by the Doctrine syntax).

If you defined your foreign keys in the database schema, you should have a static doSelectJoinByAll method in the ArticleHasTagPeer class. If you use this method, the related Article and Tag objects will be hydrated with the same query. You can still pass in a Criteria object that modifies the Article and Tag selection criteria. I know this is a bit strange, since you probably want to start from the Article objects, and this was one of the driving factors for the change in Propel 1.5. In Symfony you can also use the DbFinderPlugin, this will already give you this capability in Propel 1.3 (it needs a small patch for Propel 1.4). In fact, Propel 1.5 is mostly written by François Zaniotto, the author of the DbFinderPlugin.

Answer 3

Short answer is no.

But with some efforts you still can do that. Here's list of options:

1. Use dbFinderPlugin plugin
2. Write your own peer method (say, doSelectPostWithUsersAndComments).
3. Migrate to Propel 1.5
4. Migrate to Doctrine

Answer 4

I believe you are using symfony 1.0 and thus Propel 1.2... Whilst the methods already described in the comments talk about alternative methods, there is a direct way to at least solve your problem: add this function to your ArticlePeer class:

  public static function getTaggedArticles()
  {
    $c = new Criteria();
    //some filters here, e.g. LIMIT or Criteria::IN array
    $ahts = ArticleHasTagPeer::doSelectJoinAll($c);

    $articles = array();
    foreach($ahts as $aht)
    {
      if(!isset($articles[$aht->getArticleId()]))
      {
        $articles[$aht->getArticleId()] = $aht->getArticle();
      }

      $articles[$aht->getArticleId()]->addTag($aht->getTag());
    }

    return $articles;
  }

where $ahts is short for $article_has_tags. Create a simple array of tags in your Article class (protected array $collTags) along with the addTag() method, if they don't already exist to facilitate this.

This then only executes one SQL query, but consider seriously that without the filter I mention you are potentially hydrating hundreds of objects unnecessarily, and that is a significant performance hit. You may want to research how to hydrate based only on a doSelectRS() call - inspect your BlahPeer classes for how their JOIN methods work, and then this link for how to write custom JOIN methods.

Either way, the method builds a unique array of articles with the ArticleId as the key - if you need a different sort order, you can either sort this array again or use a different array key to organise the collection as you build it.