Example of a query that is really badly written that I want to optimize. There are three tables involved: #__rootflick_submissions, #__users, #__rootflick_votes. The votes table is a simple data set which records user_id, the submission_id, and the vote value. Counting the vote values for a submitted_id should give all the votes for that submiss…

chapter.php

<?php
public function getSubmits()
  {
		$cid = JFactory::getApplication()->input->getInt('cid');
		
		$db = $this->getDbo();
		$query = $db->getQuery(true);
		
		$query->select('a.*, i.username');
		$query->from('#__rootflick_submissions as a');
		$query->join('LEFT', '#__users as i ON (a.user_id = i.id)');
		$query->where('chapter_id='.$cid . ' and winner = 0');
		$db->setQuery($query);
		$submits = $db->loadObjectList();
		
		// loop through the submits to add in the votes.
		// this is probably not very great performance wise.
		// really hoping to find a way to optimise this.
		foreach($submits as $submit){
			$db = $this->getDbo();
			$query = $db->getQuery(true);
			
			$query->select('sum(vote)');
			$query->from('#__rootflick_vote');
			$query->where('sub_id ='.$submit->id);
			$db->setQuery($query);
			$result = $db->loadRow();
			$submit->vote = $result[0];
		}
		return $submits;
	}
?>

maybe inner join to '#__rootflick_vote', group on sub_id and sum(vote)