How to join a table in symfony (Propel) and retrieve object from both table with one query

Question

Hi,

I'm trying to get an easy way to fetch data from two joined Mysql table using Propel (inside Symfony) but in one query.

Let's say I do this simple thing:

$comment = CommentPeer::RetrieveByPk(1);
print $comment->getArticle()->getTitle();
//Assuming the Article table is joined to the Comment table

Symfony will call 2 queries to get that done. The first one to get the Comment row and the next one to get the Article row linked to the comment one.

Now, I am trying to find a way to make all that within one query. I've tried to join them using

$c = new Criteria();
$c->addJoin(CommentPeer::ARTICLE_ID, ArticlePeer::ID);
$c->add(CommentPeer::ID, 1);
$comment = CommentPeer::doSelectOne($c);

But when I try to get the Article object using

$comment->getArticle()

It will still issue the query to get the Article row. I could easily clear all the selected columns and select the columns I need but that would not give me the Propel object I'd like, just an array of the query's raw result.

So how can I get a populated propel object of two (or more) joined table with only one query?

Thanks,

JP

Answer 1

You should have a CommentPeer::doSelectJoinArticle() method, which can use to do this. If you don't, then you're probably not using MySQL's InnoDB storage engine or foreign key references in your database schema, which is something I highly recommend - not only will it allow Propel to add these extra JOIN methods to its generated model code, but you will gain many benefits from an ACID-compliant database.

The alternative is to write the CommentPeer::doSelectJoinArticle() method yourself - this link will describe the process, but the length of the process may make you choose the first option :)