Login

ridley1012 · 02-24-2015, 01:39 AM

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.

Code:
       $date = date("d-m-Y", $date);

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

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

        $this->db->select('*');

        $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('vehicle.*');

        $this->db->select('vehicle_group.*');

        $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');

        $this->db->group_by('rental.rental_index');

        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.