How to hydrate columns from JOIN tables into a Propel Object collection using custom SQL in just 1 query?

Question

I'm using Symfony 2 with the propelorm/PropelBundle (Propel 1.6) and MySQL (InnoDB with declared foreign keys), and I'm using custom SQL as shown in the section Using Custom SQL to hydrate a propel object collection, as follows:

$con = Propel::getConnection(VerbNounPeer::DATABASE_NAME);
$sql = "SELECT verb_noun.*, verb.* FROM verb_noun"
        ." JOIN verb ON verb_noun.verb_id = verb.id";
$stmt = $con->prepare($sql);
$stmt->execute();

$formatter = new PropelObjectFormatter();
$formatter->setClass('Company\SiteBundle\Model\VerbNoun');
$verb_nouns = $formatter->format($stmt);

Note: this isn't the actual query - I've put in a trivial example just to illustrate the requirements. My actual query is more complex, thus the need to use "Custom SQL" rather than propel methods.

I have not been able to hydrate the columns of the joined table into the propel object collection in one shot. The columns of the joined table ('verb') are only loaded in the view when needed through additional queries to the database (I believe this is called lazy loading, which I don't want). Is there a way to hydrate the propel object collection ($verb_nouns) with the columns of the joined table ('verb') in the initial above query to eliminate numerous queries to the database later on?

As it is, using the above code along with the one below (in the view file in Twig) results in one database connection for each loop in the foreach statement:

{% for verb_noun in verb_nouns %}
    <li>{{ verb_noun.NounId }}.&nbsp;{{ verb_noun.verb.Name }}</li>
{% endfor %}

Answer 1

You don't need to do manual hydration or a custom query, if I understand your question correctly. Just use one of the join methods like: VerbNounQuery::create()->joinVerb()->find() and that will return a collection of VerbNouns with their Nouns already populated - all in one query to the db.

I'd highly recommend an auto-completing IDE, incidentally - it will help you explore the Query system much more easily.

Answer 2

As I had this problem today as well I thought to post the solution here. Using the setClass or setPeer I was getting the constant(): Couldn't find constant XXX::PEER'-notice. So it seems u have to pass a Criteria to the ObjectFormatter for this to work :

$con = Propel::getConnection(VerbNounPeer::DATABASE_NAME);
$sql = "SELECT verb_noun.*, verb.* FROM verb_noun"
        ." JOIN verb ON verb_noun.verb_id = verb.id";
$stmt = $con->prepare($sql);
$stmt->execute();

$formatter = new PropelObjectFormatter(new VerbNounQuery());
$verb_nouns = $formatter->format($stmt);