Quebec Web Design Company

Active Record Working with Sub Queries

Admin istrator On June 7, 2011

Bookmark and Share

In cases where you need to perform two different queries, the typical way I see people do such queries are usually very inefficient. Normally performing the second query in a foreach loop for example. That means if you have a lot of results that same query is being performed every time. A classic example of this is Blog Comments. It's not unusual to see 5 or 10 blog posts on a single page. So instead of doing a query each blog post that is displayed on your page each time just to display a number of how many comments are posted. You can simply use a sub query that will only be performed the 1st time. So instead of having 6 or 11 queries you end up with 2 queries.

Below is an example of such a query in active record. Of course this kind of technique can be used in a variety of ways. So anytime you come across a situation where you need some other query data in the middle of a foreach loop than you should use a technique like below.


#Model Function
function get_blogposts($num, $offset)
{	
	$get_posts = $this->db		
		->where('blog.active', 'Y')
		->select('
		blog.blog_id,
		blog.name,
		blog.text,
		blog.active,
		blog.postedby,
		blog.date,
		blog.tags,
		(select count(blog_comments.blog_id) from blog_comments where blog_comments.blog_id = blog.blog_id) as comment_total,
		')
		->from('
			blog
		')
		->limit($num, $offset)
		->order_by('blog.date', 'desc')
		->get();
	return $get_posts;
}


So now you can perform your foreach loop and display the total comments found in only 2 queries.This might seem simple to some people but if you think about a large application with many different variables things can get messy very quickly. As well as if you have many comments made in a very active website for example. This will save you quite a bit of database load, as the procedure will only happen once instead of multiple times. Which ultimately will save you server memory as well.

They're is really almost no limitations to sub queries, you can even do sum calculations using a sub query like for example adding a total amount of multiple different rows from a database.


(select sum(ilr_team_data.points - ilr_team_data.pen_pts + ilr_team_data.bonus_pts + ilr_team_data.qual_pts) from ilr_event_result where ilr_event_result.team_id = ilr_team_data.team_id limit 1) as champ_points,





Comments

Subscribe to our mailing list

Live Help