How can I join plugin data with entry data based on uri?

Question

I have a plugin with a record (and model). The record have attributes uri and hits. The uri attribute in my record corresponds to an entry's uri, as stored in the craft_elements_i18n table.

What is the most efficient way to return entries with the uris I have stored, sorted on hits? Is there a way to join the uri attribute in my record with the uri in the craft_elements_i18n table, and return entry data and hits in one go?

Right now I have this, which does the job but isn't very efficient:

$results = $this->queryResultRecord->getQueryResultsBySetId($querySet->id);

if ($results) {
    foreach ($results as $result) {
        $criteria = craft()->elements->getCriteria(ElementType::Entry);
        $criteria->uri = $result->uri;
        $entries = $criteria->find();

        if ($entries) {
            $entry = $entries[0];
            $return[] = array('entry'=>$entry, 'hits'=>$result->hits);
        } 
    }
}

The getQueryResultsBySetId method return the data from my record sorted by hits. The $return variable is returned back to my frontend templates where I can do:

{% for result in results %}
    {{ result.entry.title }} - {{ result.hits }}<br>
{% endfor %}

Answer 1

You could of course manually create a select query with a join that fetches entries ordered by hits descending, however you will basically be recreating what $criteria->find() does above. I imagine this will introduce an almost negligible performance improvement, but it really depends on how many entries you are returning.

The following code should give you a starting point:

$query = craft()->db->createCommand()
    ->from('pluginTable pluginTable')
    ->join('elements_i18n elements_i18n', 'elements_i18n.uri = pluginTable.uri')
    ->join(...)
    ->order('pluginTable.hits desc');