[eluser]Nick Woodhead[/eluser]
Hi everyone,
I'm hoping that someone may be able to point me in the right direction here with an SQL statement that I'm trying to create with active record.
The basics of my 3 MySql tables are :
Active_properties[ fla_id, property_id, agent_id, date_activated ] where fla_id is the primary key and property_id is a forieign key to another table.
Listing_agreements[ listing_id , fla_id, commission_rate ]
Purchase_agreements[ purchase_id, fla_id, sale_price]
There can be multiple entries in the active properties table for the same property id with different activation dates. There can be only one entry in listing agreements and purchase agreements for each property where fla_id in these 2 table corresponds to the fla_id of the active property with the most recent activation date. I think I explained this clearly enough, I'm sorry if not.
What I need to get is the number of fla_ids with unique property ids, the number of purchase agreements and the sum of the total commissions sum(listing_agreements.commission_rate*purchase_agreements.sale_price) for an agent id and I hopefully want to be able to to it with only one database request. My attempt is returning the no of purchase agreements and total commissions correctly but for some reason dosnt seem to be grouping by the property id.
Here is my attempt using active record:
$this->db->select('
COUNT(active_properties.fla_id) AS total_flas,
COUNT(purchase_agreements.purchase_agreement_id) AS executed_flas,
SUM(purchase_agreements.sale_price*listing_agreements.commission_rate) AS total_commission
');
$this->db->from('active_properties');
$this->db->join('listing_agreements','listing_agreements.fla_id = active_properties.fla_id','LEFT');
$this->db->join('purchase_agreements','purchase_agreements.fla_id = active_properties.fla_id','LEFT');
$this->db->where('active_properties.agent_id', $agent_id);
$this->db->group_by('active_properties.property_id');
//$this->db->distinct();
$query = $this->db->get();
if($query->num_rows() > 0){
$rows = $query->result();
foreach($rows as $row) {
echo '<br><br>';
print_r($row);
}
}
Thank you very much in advance for any help anyone can give me!!
