0

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 %}
RayOnAir
  • 2,038
  • 2
  • 22
  • 33

2 Answers2

0

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
  • Hello @halfer, I'm actually looking to use "Custom SQL" since the actual query that I'm trying to do is more complex than the simplified version above. I know Using propel methods works (I actually did the more complex query with propel methods as well) but it is faster and a bit less cumbersome to do what I'm trying to do with "Custom SQL". Hence, my question has that constraint in mind (to use "custom SQL")... – RayOnAir May 01 '12 at 21:42
  • Right, no problem - it might be worth editing your question though to make it clearer. The logical response otherwise is probably: "you don't need custom SQL for that query"! As for an answer: I would definitely try to do it with the Query class. I'd try using the `select()` method to supply column names, I think as an array. I'm not sure how to specify the raw SQL, but it should be possible inside a Query - check autocompletion if the docs fail `:)`. – halfer May 01 '12 at 22:01
  • Thanks @halfer for the edits and suggestions. I'll keep looking for an elegant solution... – RayOnAir May 02 '12 at 14:36
  • 1
    No probs. Also, don't get stuck on this for ages: just hydrate one table first, and have others load on demand. It will be slower than is perhaps ideal, but get it working and committed - often performance isn't as bad as one expects. Don't optimise too early `:)` – halfer May 02 '12 at 14:46
0

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);
DarkBee
  • 16,592
  • 6
  • 46
  • 58