Welcome Guest, Not a member yet? Register   Sign In
Joined Min/Max and 'Having'

More a programming issue rather than a codeigniter issue. Got a bit of an issue with using 'Having' and joined tables. It works but its horrendously slow, looking for any input that may speed it up.
       $date = date("d-m-Y", $date);

       $from_date = strtotime($date . " 00:00:00");
       $to_date = strtotime($date . " 23:59:59");

       $this->db->select('rental.rental_index as rental_index');
       $this->db->select('collect_branch.branch_name as collection_branch');
       $this->db->select('return_branch.branch_name as return_branch');
       $this->db->select('MIN(rental_date_time.collection_date_time) as rental_from_date');
       $this->db->select('MAX(rental_date_time.return_date_time) as rental_to_date');
       $this->db->join('rental_date_time', 'rental_date_time.rental_index = rental.rental_index', 'LEFT');
       $this->db->join('hirer_rental', 'rental.rental_index = hirer_rental.rental_index', 'LEFT');
       $this->db->join('hirer', 'hirer_rental.hirer_index = hirer.hirer_index', 'LEFT');
       $this->db->join('address', 'hirer_rental.address_index = address.address_index', 'LEFT');
       $this->db->join('branch as collect_branch', 'rental.collection_branch_index = collect_branch.branch_index', 'LEFT');
       $this->db->join('branch as return_branch', 'rental.return_branch_index = return_branch.branch_index', 'LEFT');
       $this->db->join('vehicle', 'rental.vehicle_index = vehicle.vehicle_index', 'LEFT');
       $this->db->join('vehicle_group', 'vehicle.vehicle_group_index = vehicle_group.vehicle_group_index', 'LEFT');

       if($branch_index != '0')
           $this->db->where('collection_branch_index', $branch_index);

       $this->db->where('rental_status != ', 'Deleted');
       $this->db->where('rental_status != ', 'Cancelled');
       $this->db->where('rental_status != ', 'Off Road');
       $this->db->having('rental_from_date > ', $from_date);
       $this->db->having('rental_from_date < ', $to_date);
       $this->db->order_by('rental_from_date', 'ASC');
       return $this->db->get('rental')->result();

Everything works and it does return the correct results by as I've said it is very slow. If I remove the date range 'Having' then it works a lot better but obviously this part is necessary. Any help would be greatly appreciated.

Messages In This Thread
Joined Min/Max and 'Having' - by ridley1012 - 02-24-2015, 01:39 AM
RE: Joined Min/Max and 'Having' - by obiron - 02-24-2015, 05:46 AM

Theme © iAndrew 2016 - Forum software by © MyBB