Solved

Trying to simplify a series of redundant codeigniter Active Record queries

Posted on 2013-10-29
4
1,079 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39608237
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
ID: 39608252
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 43

Expert Comment

by:Chris Stanyon
ID: 39608271
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
ID: 39608316
Thanks again Chris.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

752 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