Solved

Trying to simplify a series of redundant codeigniter Active Record queries

Posted on 2013-10-29
4
1,090 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This article discusses how to implement server side field validation and display customized error messages to the client.
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 and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

632 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