Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2014-09-09T08:09:10.837Z
Answers.Text: Ok, I guess that'll be a bit more work than what it's worth. :)
Answers.UserDisplayName: André Elvan
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2014-09-07T19:33:23.663Z
Answers.LastActivityDate: 2014-09-07T19:33:23.663Z
Answers.UserDisplayName: Ben Croker
Body: <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>
  
Tags: <plugin-development><elementcriteriamodel><sorting><records><join>
Title: How can I join plugin data with entry data based on uri?
ThreadText: # 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 }}<br>
  {% 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. :)  
  
  ---
  
  