2

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 %}
3

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');
  • Ok, I guess that'll be a bit more work than what it's worth. :) – André Elvan Sep 9 '14 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.