This site uses cookies and similar technologies.

If you don't change browser settings, you agree to it.

I understand
40 Posts
RatingAction
10 months ago
Topic

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.

Get a Book for SEBLOD
40 Posts
RatingAction
9 months ago
0
Level 1

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! 

Get a VIP membership