0

I have a series of super search results listing pages displaying lists that are all rated with the Ratings module.

I want to get the average rating of all the entries in the list to put into schema markup (as opposed to the average rating for each entry which is what the exp:rating:stats tag would do).

I thought I could do it with the query module because I've used it for a similar case elsewhere on the site, except that there it's working out the average of all the ratings on the site, not just those from a particular page.

I can save the list of IDs to a variable with Stash:

  {exp:super_search:results}
        {exp:stash:append_list
              name='ids'
              context="@URI"
        }{stash:id}{entry_id},{/stash:id}{/exp:stash:append_list}
  {/exp:super_search:results}

which outputs as expected, i.e. 1,2,3 etc. but if I try to input that into a query like

  {exp:query
        sql="SELECT count(entry_id) as `count`, avg(rating) as `avg` FROM exp_ratings WHERE entry_id IN ({exp:stash:get_list
        name='ids'
        backspace='1'
        process='end'
        context='@URI'
  }{id}{/exp:stash:get_list})"
        parse='inward'
  }
 {/query}

I get a WSOD.

I've also tried the above with all the line breaks removed.

If I replace the get_list in the query with the actual values that the list outputs if used outside the query, e.g.

  {exp:query
        sql="SELECT count(entry_id) as `count`, avg(rating) as `avg` FROM exp_ratings WHERE entry_id IN (1,2,3)"
        parse='inward'
  }
 {/query}

I get the expected output. So the query itself is fine, it's just trying to use a dynamic Stash variable which seems to be causing the WSOD.

Does anyone have suggestions as to how I could accomplish the task?

  • What version of EE? Perhaps closing the exp:query tag will clear WSOD? If you can get to point where you don't have WSOD you can see the SQL being submitted to exp:query listed in the debug output, so if it is still not giving correct results you can at least see what SQL is being submitted. – JCOGS Design May 27 '19 at 10:30
  • I've edited the question to add some more detail. The closing tag is there, I hadn't included it in the question. EE version is 2.x. – Tyssen May 28 '19 at 0:16
0

After a bit more searching I found that passing variables into EE's query module to be problematic with a lot of workarounds using PHP variables or plugins instead.

So I wrote a quick plugin to handle the query and ended up doing this in the template:

{exp:stash:parse process='end'}
  <script type='application/ld+json'>
  {
    "@context": "http://schema.org/",
    "@type": "CollectionPage",
    "aggregateRating": {
      "@type": "AggregateRating",
      "ratingCount": "{super_search_total_results}",
      "ratingValue": "{exp:td_tours_average_rating ids="{exp:stash:get_list name='st_schema_ids' backspace='1' context='@URI'}{schema_id}{/exp:stash:get_list}" parse="inward"}"
    }
  }
  </script>
  {/exp:stash:parse}
| improve this answer | |

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.