Hi, I'm still working on my movie website. I'm trying to combine the worlds of action movies and quantitative analysis. To do this I have stored a lot of information in my movie content types and have several linked content types such as actors, directors, main characters etc.
What I want to do now is to aggregate this quantitative information by various dimensions. One example would be the average bodycount a certain main character (e.g. the Terminator) has across all of his movies. I want to be able to do this on the fly so that I don't have the update every related article after addig a movie. The movie content type should hold all the information including the links to the other content types and the other content types should get a substantial part of their information from the movie content type.
I have played around with the code pack and the SQL pack. I believe I need to use a Before Render field to pull the information via an SQL query and then store it on the fly in a field for the content item I am rendering. For pulling the average bodycount ("BC") for a main character ("actionheld") whose content view I am loading I believe the SQL query would have to be something like this:
SELECT AVG BC FROM #__cck_store_item WHERE actionheld = 'cck->id'
However, I can't figure out how to run this SQL query in a before render field. I have read the "Using Code Pack"-tutorial, however, I am afraid I need some more guidance as I don't understand the coding enough.
Can anyone point me in the right direction on how to pull data from the SQL-database and calculate averages / sums on the fly?
EDIT: To give you an example of the underlying information, here is a link to the character "Terminator": https://ratingaction.com/filme-und-mehr/actionhelden/terminator As you can see, I've been able to set up a list module that lists all movies where I have selected "Terminator" as main character. Now I would like to be able to calculate averages, sums etc. for the figures like BC, S5+ etc.
I can't believe it myself, but I got this to work. There was a lot of trial and error involved, but this is the code that made it work for me:
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
->select('AVG(bc) AS value_sum')
->from($db->quoteName('#__cck_store_item_content'))
->where($db->quoteName('actionheld')." = ".$fields['art_id']->value);
$db->setQuery($query);
$result = $db->loadResult();
$result = round($result);
$fields['bc_avg']->value = $result;To make this work with the above code in the BeforeRender field I also added the article id field to the content view as hidden.
I'm actually quite excited about this, as it will allow me to run a number of quantitative analyses across the data set!