sorting data error codeigniter

Question

I have some problems with sorting datas when i try type distance for example to 5 i have this error:

 Unknown column 'dystans' in 'having clause'

 SELECT COUNT(*) AS `numrows` FROM `meet` JOIN `category` ON `category`.`id` = `meet`.`category_id` HAVING `dystans` <= '5' ORDER BY `when` ASC

Filename:    C:/xamppNew/htdocs/dzielimypasje/application/controllers/Meetings.php

Line Number: 177

Im using codeigniter. Line 177 is this

$data['count'] = $count = $this->db->count_all_results();

My controller:

$lat = $this->session->userdata('lat');
$lng = $this->session->userdata('lng');

        // To pagination
$this->load->library('pagination');
$limit = 10;
$offset = $this->uri->segment(4);

$this->db->start_cache();

    $this->db->select('*, meet.id, 
        (6731 * acos( cos( radians( '.$lat.')) * cos( radians( meet.lat)) *
   cos( radians( meet.lng) - radians( '.$lng.')) + sin( radians( '.$lat.')) *
   sin( radians( meet.lat)))) AS dystans');

         //data from search engine
    $level = $this->input->post('level');
    $cat = $this->input->post('category');
    $dystans = $this->input->post('dystans');
    $when = $this->input->post('when');

    if ($level) {
    $this->db->where('level', $level); 
    }
    if ($cat) {
    $this->db->where('category_id', $cat); 
    }
    if ($when) {
    $this->db->where('when <=', $when); 
    }
    if ($dystans) {
    $this->db->having('dystans <=', $dystans); 
    }
    $this->db->order_by('when', 'ASC');
    $this->db->from('meet');
    $this->db->join('category', 'category.id = meet.category_id');

$this->db->stop_cache();

        // count for pagination
    $data['count'] = $count = $this->db->count_all_results();

        // to pagination
    $this->db->limit($limit, $offset);

    $data['meetings'] = $this->db->get();
    $this->db->flush_cache();

    $config = some config for pagination ...

    $this->pagination->initialize($config); 

    $data['pagination'] = $this->pagination->create_links();


    $this->load->view( 'site/meetings/index' , $data );

view:

   <select type="text" name="dystans" placeholder="distance">
           <option value="">Odległość</option>
           <option value="1">Do 1 km</option>
           <option value="2">Do 2km</option>
           <option value="5">Do 5km</option>
           <option value="10">Do 10km</option>
   </select>

Any idea how to solve this ?? Propably its something with cache but im not sure, im new in this.

Answer 1

Alright. I dug into this and fixed everything I could find. I ditched active record for straight database access with query() function because...well...I have never liked active record and personally believe it is much much cleaner to write your query in an admin tool and copy it into your model. This gives you access to ALL of mysql's capabilities without having to jump through hoops (not to mention if you wind up using a different code base in the future you already have your queries and don't have to dig back through all of the active record code).

The main reason you were breaking at the dystans block was because you were trying to use a dynamically generated column within your where clause. The where clause only knows about columns that exist within the tables you have joined on. So to do what you are needing you have to run back through your formula.

I have also broken this out so that all of the filters will work in conjunction with one another. A user can now use filter when with filter dystans and the query will be build accordingly.

I haven't tested this code because obviously I do not have your database but it is the same way I code all of my applications. If you have trouble please let me know.

Also you should really consider breaking out your business logic or at least database interaction into a codeigniter model. Controllers should only ever act as a router between your models and views.

The link to my post on codeigniter pagination

<?php

$lat = $this->session->userdata('lat');
$lng = $this->session->userdata('lng');

// To pagination
$this->load->library('pagination');
$limit = 10;
$offset = (isset($this->uri->segment(4)) && is_numeric($this->uri->segment(4))) ? $this->uri->segment(4) : 0;

$bindArray = array();

$query = "SELECT *,"
        . " (6731 * acos(cos( radians(?)) * cos( radians( meet.lat))"
        . " * cos( radians( meet.lng) - radians(?)) + sin( radians(?))"
        . " * sin( radians( meet.lat)))) AS dystans";

$bindArray[] = $lat;
$bindArray[] = $lng;
$bindArray[] = $lat;


$query .= " FROM meet JOIN category ON category.id = meet.category_id"
        . " WHERE TRUE = TRUE";

//data from search engine
$level = $this->input->post('level');
$cat = $this->input->post('category');
$dystans = $this->input->post('dystans');
$when = $this->input->post('when');

if($level){
    $query .= " AND meet.level = ?";
    $bindArray[] = $level;
}
if($cat){
    $query .= " AND meet.category_id = ?";
    $bindArray[] = $cat;
}
if($when){
    $query .= " AND meet.when <= ?";
    $bindArray[] = $when;
}
if($dystans){
    $query .= " AND (6731 * acos(cos( radians(?)) * cos( radians( meet.lat))"
            . " * cos( radians( meet.lng) - radians(?)) + sin( radians(?))"
            . " * sin( radians( meet.lat)))) <= ?";
    $bindArray[] = $dystans;
}

$query .= "LIMIT ?,?";
$bindArray[] = $offset;
$bindArray[] = $limit;

$query_result = $this->db->query($query, $bindArray);

$data['count'] = $query_result->num_rows();
$data['meetings'] = $query_result->result();

$config = 'YOUR PAGINATION CONFIGURATION';

$this->pagination->initialize($config); 

$data['pagination'] = $this->pagination->create_links();


$this->load->view( 'site/meetings/index' , $data );