Thread

I’m putting together a ratings site, where each product has a single “Our Opinion” rating created by the entry author, and separate, member ratings created by users.

The member ratings are being handled by DevDemon’s Channel Ratings, so no problem there. The per-entry “Our Opinion” rating is actually an average based on 3 other ratings: “instructions”, “installation”, and “ease of use”. I have those fields set up in the publish page, and am using the MX Calculator math plugin to calculate the average “Our Opinion” rating per-entry:

{exp:channel:entries channel="products"}
  Our Opinion: {exp:mx_calc expression='({products_rating_ease_of_use}+{products_rating_installation}+{products_rating_instructions})/3'}
{/exp:channel:entries}

The problem is I need to be able to sort entries based on this average “Our Opinion” rating. But since that rating isn’t an actual field, I’m not really sure how that would work. I’m assuming I’ll have to get my hands dirty with some custom queries, but I don’t really know where to start. Any suggestions are greatly appreciated, thanks!

Have you investigated the VZ Average plugin? http://devot-ee.com/add-ons/vz-average Not sure whether it will do what you need or not.

If not, a custom query to sort by a calculated field is pretty easy. For the sake of argument, we’re going to say that your fields are field_id_1, field_id_2, and field_id_3. Here’s how you’d get a sorted list of entry IDs (you could then use one of several methods to pass the list to the fixed_order parameter of a channel_entries tag, for example.)

SELECT entry_id, ((CAST(field_id_1 AS SIGNED) + CAST(field_id_2 AS SIGNED) + CAST(field_id_3 AS SIGNED))/3) AS opinion
FROM exp_channel_entries
WHERE channel_id = {{whatever your channel ID is}}
ORDER BY opinion DESC

VZ Average isn’t quite going to do what you want. A custom query like Adrienne suggested will probably be your best bet.

Thanks Adrienne, that’s awesome! A query definitely would be better for me so the client can add ratings directly in the publish page.

Would you be wiling to explain exactly what’s going on in that query so I understand it better?

I placed the custom query Adrienne suggested into a snippet named “rating_order” that outputs the entry ids separated by pipe characters:

{exp:query sql="SELECT entry_id, ((CAST(field_id_34 AS SIGNED) + CAST(field_id_35 AS SIGNED) + CAST(field_id_36 AS SIGNED))/3) AS our_opinion FROM exp_channel_data WHERE channel_id = 2 ORDER BY our_opinion DESC"}{if count != 1}|{/if}{entry_id}{/exp:query}

I then tried using that snippet inside a channel entries tag:

{exp:channel:entries channel="products" fixed_order="{rating_order}"}

But that didn’t work. The template debugger shows that the channel entries tag is being read like this:

{exp:channel:entries channel="products" fixed_order="M00o93H7pQ09L8X1t49cHY01Z5j4TT91fGfr"}

Does anybody know how I could pass this custom sort order to my channel entries tag? Or a better way to do it altogether?

I think the ideal way to do this would be a custom field that is automatically populated with the average value of the other rating fields. That way I could just use the channel entries tag’s native orderby=“CUSTOM_FIELD” parameter and the “Our Opinion” rating could be stored with each channel entry. Does anybody know of a fieldtype like that?

Not sure if this is helpful but this really reminds me of this article by @low http://loweblog.com/freelance/article/fowd-ee-and-a-little-tip/  (the php code here is still for EE1 so you’ll have to adapt it slightly).

Also take a look at stash http://devot-ee.com/add-ons/stash - this is the kind of scenario that add-on was made for.
There are several examples in the devot-ee forum, this one for example http://devot-ee.com/add-ons/support/stash/viewthread/7323 which is trying to do something similar (piped entry ids as input for a channel:entries loop) to what you’re looking after. Hope this helps!

Thanks for the info @e-man!

I ended up writing (with help) a dead-simple extension that updates a separate custom field with the average value of the other rating fields. That way I can use the field as a variable in the template and use it in the channel entries “orderby” parameter. Here’s the gist of it:

public function update_average($entry_id,$meta,$data)
 {
  // Check if entry is in the 'products' channel
  if($data['channel_id'] != 2)
  return;

  // Average the values of the individual rating fields
  $average = ($data['field_id_34'] + $data['field_id_35'] + $data['field_id_36']) / 3;

  // Update the Our Opinion field in the DB with the average value
  $this->EE->db->update(
   'exp_channel_data',
   array('field_id_41' => $average),
   array('entry_id' => $entry_id)
  );
 }

This runs on the “entry_submission_absolute_end” hook.

Isn’t that easy?!

Cool, glad you got it sorted 😊 (I really need to get into extensions/modules!)