如何在symfony（Propel）中连接表，并使用一个查询从两个表中检索对象

Question

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 :)