Solved

Trying to simplify a series of redundant codeigniter Active Record queries

Posted on 2013-10-29
4
1,037 Views
Last Modified: 2013-10-29
Hi,
Below are a series of very similar active record queries I'm running. I'd like to simplify them as it seems quite redundant but not sure how? They're all getting some stats for the past 30 days.

First off, any idea how to make it 'stats for the current month' not the past 30 days? My 'created' column is a datetime stamp.

Query 1 and 5 are related. 1 gets the total amount of trades and the fifth query gets their total dollar value. Seems 'possible' to combine those 2?

Secondly, the 'CURRENT, DEAL AND NO DEAL' (queries 2, 3, and 4) are the same column. Be much better if I could somehow simply count how many of each in one query instead of having to run 3 queries on the same column to get how many of each.

And lastly they all use the same time interval... seems redundant to reiterate it 5 times over?
// Get Stats for frontend

function get_stats($data) {
	
	$parent_id = $data['parent_id'];
	
// get total amount of trades
	$this->db->select('id');
	$this->db->from('trades');
	$this->db->where('parent_id', $parent_id);
	$this->db->where('created > DATE_SUB(CURDATE(),INTERVAL 30 DAY)');  
	$query = $this->db->get();
	$data['total'] = $query->num_rows();
	
//get amount of trades whose status is CURRENT	
	$this->db->select('id');
	$this->db->from('trades');
	$this->db->where('parent_id', $parent_id);
	$this->db->where('status', 'CURRENT');
	$this->db->where('created > DATE_SUB(CURDATE(),INTERVAL 30 DAY)');  
	$query = $this->db->get();
	$data['current'] = $query->num_rows();
	
//get amount of trades whose status is DEAL	
	$this->db->select('id');
	$this->db->from('trades');
	$this->db->where('parent_id', $parent_id);
	$this->db->where('status', 'DEAL');
	$this->db->where('created > DATE_SUB(CURDATE(),INTERVAL 30 DAY)');
	$query = $this->db->get();
	$data['deal'] = $query->num_rows();
	
//get amount of trades whose status is NO DEAL	
	$this->db->select('id');
	$this->db->from('trades');
	$this->db->where('parent_id', $parent_id);
	$this->db->where('status', 'NO DEAL');
	$this->db->where('created > DATE_SUB(CURDATE(),INTERVAL 30 DAY)');
	$query = $this->db->get();
	$data['nodeal'] = $query->num_rows();

//total dollar amount for all trades combined
	$this->db->select('SUM(finalTradeValue) AS tradeTotal', FALSE);
	$this->db->from('trades');
	$this->db->where('parent_id', $parent_id);
	$this->db->where('created > DATE_SUB(CURDATE(),INTERVAL 30 DAY)');
	$query = $this->db->get();
	$data['tradeamount'] = $query->row_array();

	return $data;
}

Open in new window

Any help is much appreciated.
tj
0
Comment
Question by:tjyoung
  • 2
  • 2
4 Comments
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
Comment Utility
Have a look at this. I think you'll need 2 queries - one for the count and value and another for the STATUS breakdown:

$parentId = $data['parent_id'];
$today = new DateTime();
$monthStart = new DateTime('First Day of this Month');

$this->db
	->select('COUNT(id) as count')->select('SUM(finalTradeValue) as value')
	->from('trades')
	->where('parent_id', $parentId)->where('created >=', $monthStart->format('Y-m-d'))->where('created <=', $today->format('Y-m-d'));	

//get 1 row with 2 columns - count and total
$result = $this->db->get()->row();
echo $result->count;
echo $result->value;




$this->db
	->select('status')->select('COUNT(id) AS count')->select('SUM(finalTradeValue) AS value')
	->from('trades')
	->where('parent_id', $parentId)->where('created >=', $monthStart->format('Y-m-d'))->where('created <=', $today->format('Y-m-d'))
	->group_by('status');
	
//get a row for each status, along with the count and value
$result = $this->db->get()->result();
foreach ($result as $row):
	echo $row->status;
	echo $row->count;
	echo $row->value;
endforeach;

Open in new window

In theory, you could just use the last query and in the foreach loop, sum the count and the value manually:

$totalValue = 0;
$totalCount = 0;
foreach ($result as $row):
	echo $row->status;
	echo $row->count;
	echo $row->value;
	$totalValue = $totalValue + $row->value;
	$totalCount = $totalCount + $row->count;
endforeach;

echo $totalValue;
echo $totalCount;

Open in new window

0
 
LVL 1

Author Comment

by:tjyoung
Comment Utility
Hi
Is the first day of month
Php 5.3+?
I'm stuck at 5.2
Way to express that for 5.2?
Driving to work so my response is kinda brief :)
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Ahh. Yeah - that's 5.3. In 5.2, do something like:

$monthStart = new DateTime(date('m-01-Y'));

Open in new window

0
 
LVL 1

Author Closing Comment

by:tjyoung
Comment Utility
Thanks again Chris.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now