Get related entry data within a plugin (ElementCriteriaModel) without extra queries

Question

I have the following code:

$criteria = craft()->elements->getCriteria(ElementType::Entry);
$criteria->section = 'submissions';

// Loop
foreach ($criteria as $entry)
{
    echo $entry->id.'<br />';
    echo $entry->title.'<br />';
    echo $entry->sport.'<br />';
}

The sport field is an Entry field, pointing to an entry in a different channel. My question is, how can I get the data from the related entry (i.e. the title) without an extra query per row? Can I eager load it? Or get the sport entries in a separate query and then combine the data?

I need to get the title of the related sport entry. The following works but gives an extra query for every row:

$entry->sport->first()['title'];

Anyone? Thanks in advance

Answer 1

I fixed this now by using the method buildElementsQuery, like so:

    $query = craft()->elements->buildElementsQuery($criteria, $contentTable, $fieldColumns);

After that, I was able to add custom joins to the query:

$query
    ->addSelect('sport.title sportTitle')
    ->leftJoin('relations sportRelation', 'sportRelation.sourceId = elements.id AND sportRelation.fieldId = 71')
    ->leftJoin('content sport', 'sport.elementId = sportRelation.targetId')

This gave me access to the data I need, without running extra queries. Thanks go out to Bob Olde Hampsink for showing me this.

I'd love to hear though if there is a simpler way that won't require custom SQL, but for now this will do the trick.