<p>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 <em>$criteria->find()</em> does above. I imagine this will introduce an almost negligible performance improvement, but it really depends on how many entries you are returning. </p>

<p>The following code should give you a starting point:</p>

<pre><code>$query = craft()-&gt;db-&gt;createCommand()
    -&gt;from('pluginTable pluginTable')
    -&gt;join('elements_i18n elements_i18n', 'elements_i18n.uri = pluginTable.uri')
    -&gt;join(...)
    -&gt;order('pluginTable.hits desc');
</code></pre>

CC BY-SA 3.0
2014-09-09T08:09:10.837Z
Ok, I guess that'll be a bit more work than what it's worth. :)
André Elvan
CC BY-SA 3.0
2014-09-07T19:33:23.663Z
2014-09-07T19:33:23.663Z
Ben Croker
<p>I have a plugin with a record (and model). The record have attributes <strong>uri</strong> and <strong>hits</strong>. The <strong>uri</strong> attribute in my record corresponds to an entry's uri, as stored in the craft_elements_i18n table. </p>

<p>What is the most efficient way to return entries with the uris I have stored, sorted on <strong>hits</strong>? 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?</p>

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

<pre><code>$results = $this-&gt;queryResultRecord-&gt;getQueryResultsBySetId($querySet-&gt;id);

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

        if ($entries) {
            $entry = $entries[0];
            $return[] = array('entry'=&gt;$entry, 'hits'=&gt;$result-&gt;hits);
        } 
    }
}
</code></pre>

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

<pre><code>{% for result in results %}
    {{ result.entry.title }} - {{ result.hits }}&lt;br&gt;
{% endfor %}
</code></pre>

<plugin-development><elementcriteriamodel><sorting><records><join>
How can I join plugin data with entry data based on uri?
# How can I join plugin data with entry data based on uri?

(Asked by: username_0 on 2014-09-07)

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 }}
{% endfor %}

**Tags:** plugin-development, elementcriteriamodel, sorting, records, join

---

## 1 answers

### Answer by username_1 on 2014-09-07. Score: 3 | Accepted answer

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');

> **Comments:**
> **username_0**: Ok, I guess that'll be a bit more work than what it's worth. :)  

---

