Select/where - Query run twice?

DaxCax · March 2013

Branch 1.0 beta
It's an advanced module.

I'm trying to pass WHERE-statement to list_items() method from my controller in items(). But the result is wrong. When debugging I get the following queries:

Top left:


SELECT `tm_Course`.* FROM (`tm_Course`) WHERE `tm_Course`.`tourevent_id` = '12'

Main window:


SELECT `tm_Course`.* FROM (`tm_Course`) WHERE `tm_Course`.`tourevent_id` = 0 ORDER BY `courseName` asc LIMIT 50

The first one is correct and tested against DB. The second isn't? How come it's changed?

This is my controller "courses.php":


<?PHP
require_once(FUEL_PATH . '/controllers/module.php');

class Courses extends Module {

    function __construct() {
        parent::__construct();
    }

    public function _remap($method, $params = array()) {
        if (method_exists($this, $method)) {
            return call_user_func_array(array($this, $method), $params);
        }
        show_404();
    }

//    function index() {
//
//        $this->items();
//    }

    function items() {

        $tourevent_id = uri_segment(5);
        $where = array('tm_Course.tourevent_id' => $this->uri->segment(5));
        $this->courses_model->db()->where($where);
   
//        $this->courses_model->db()->where(array('tm_Course.tourevent_id' => 12));
//        $this->courses_model->debug_query();

        parent::items();
    }

Even if I hardcode it, like the commented section, doesn't matter.

This is my model "courses_model.php":


<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');
require_once(FUEL_PATH . 'models/base_module_model.php');

class Courses_model extends Base_module_model {

    public $required = array('courseName', 'tourevent_id', 'Division_id', 'RoundType_id');
    public $foreign_keys = array(
        'tourevent_id' => array('tour' => 'tour_events_model'),
        'Division_id' => array('tour' => 'divisions_model'),
        'RoundType_id' => array('tour' => 'round_types_model')
    );

    function __construct() {
        parent::__construct('tm_Course'); // table name
    }

    public function list_items($limit = NULL, $offset = NULL, $col = NULL, $order = NULL) {

//        $this->db->where(array('tm_Course.tourevent_id' => 12));

        $data = parent::list_items($limit, $offset, $col, $order);
        
        $this->debug_query();
        
        return $data;
    }

    function _common_query() {

        parent::_common_query(); // to do active and published
    }


class Course_model extends Base_module_record {
    
}

If I hardcode the WHERE-statement here it will work.

Any suggestions?

Thanks!

admin · March 2013

I believe the reason is because the parent class method (Module::items()) calls both courses_model->list_items() and courses_model->list_items_total() with the latter used to get the total number of items for pagination. The method "list_items_total" calls the method "list_items()" to apply all the filtering logic before getting the total without the limit clause on the query.

So in short, extra where conditions should be placed in the model's list_items method so that both the "list_items()" method and the "list_items_total()" have the where condition applied to them.

DaxCax · March 2013

And how do I do that? I don't want any static WHERE-statments there or?

Thanks for your time!

DaxCax · March 2013

Can I pass variables to list_items() from my controller in another way?

Reason is, now I have a links in my "Events"-list containing an "Event"-id. Through these I want to show only the courses that belongs to each event in a list.

admin · March 2013

Perhaps try something like this in your Courses_model:

public function list_items($limit = NULL, $offset = NULL, $col = NULL, $order = NULL) {
        $where = array('tm_Course.tourevent_id' => $this->uri->segment(5));
        $this->db->where($where);
        $data = parent::list_items($limit, $offset, $col, $order);
        $this->debug_query();
        return $data;
}

DaxCax · March 2013

Don't want to make it static, but I could of course use a conditional statement. Then I still would be able to get the full list by default.

Thanks again!

DaxCax · March 2013

Tried it out, unfortunately result is the same as in first post. Seems to be it cannot extract the URI segment the second time around. If I put a static number instead of URI segment, like so:

$where = array('tm_Course.tourevent_id' => 12);

It works, and I get my filtered list correctly.
Can I use any other method to pass variables to list_items()? Or could I tell list_items_total() to use the URI segment?

admin · March 2013

Is it perhaps that the URI segment isn't there for that particular request? The list_items content is Ajaxed in and I'm guessing that URI segment doesn't exist on that Ajax call (even though you see it in the browsers URI path). If you use the console and filter on Ajax calls, you should see a call to something like: "?search_term=&limit=50&view_type=list&offset=0&order=asc&col=author&fuel_inline=0". This is set in the fuel/modules/fuel/assets/js/fuel/controller/BaseFuelController.js file. This is controlled by the "this.tableAjaxURL" value in that file.

So, in short, it appears that you need to pass that value to the Ajax request. A way around this would be to use the "filter" mechanism for a simple module. In your model, you can add the following method:

...
function filters()
{
	return array('segment' => array('type' => 'hidden', 'value' => uri_segment(5)));
}
...

This essentially creates a hidden field right next to the search field with that value stored and can be accessed as a $_GET variable from within your model's list_items method in that Ajax request.
$where = array('tm_Course.tourevent_id' => $this->input->get('segment'));

DaxCax · March 2013

Yep, took me a while to get to your conclusion about the the ajax calls. Thanks!
But i didn't know how to go from there and your simple solution seems the way to go. Close... but not all the way. Now I get this:


 A Database Error Occurred

Error Number: 1054

Unknown column 'tm_Course.segment' in 'where clause'

SELECT `tm_Course`.* FROM (`tm_Course`) WHERE `tm_Course`.`tourevent_id` = '12' AND (LOWER(tm_Course.segment) LIKE "%12%") ORDER BY `courseName` asc LIMIT 50

Filename: /var/www/kfk_fuel/fuel/modules/fuel/models/base_module_model.php

Line Number: 268

I can only see it if I put some debug-echoes in the model, else it's just the loader animation.

Why does the it include "AND (LOWER(tm_Course.segment) LIKE "%12%")" in the WHERE clause?

courses.php (controllers)


<?PHP

require_once(FUEL_PATH . '/controllers/module.php');

class Courses extends Module {

//    public $view_location = 'tour';

    function __construct() {

        parent::__construct();

    }

    public function _remap($method, $params = array()) {

        if (method_exists($this, $method)) {
            return call_user_func_array(array($this, $method), $params);
        }
        show_404();
    }

    function index() {

        $this->items();
    }

    function items() {

        parent::items();
    }

    function event() {

        $this->items();
    }

}

courses_model.php (models)


<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');
require_once(FUEL_PATH . 'models/base_module_model.php');

class Courses_model extends Base_module_model {

    public $required = array('courseName', 'tourevent_id', 'Division_id', 'RoundType_id');
    public $foreign_keys = array(
        'tourevent_id' => array('tour' => 'tour_events_model'),
        'Division_id' => array('tour' => 'divisions_model'),
        'RoundType_id' => array('tour' => 'round_types_model')
    );

    function __construct() {
        parent::__construct('tm_Course'); // table name
    }

    function filters() {

        return array('segment' => array('type' => 'hidden', 'value' => uri_segment(5)));
    }

    public function list_items($limit = NULL, $offset = NULL, $col = NULL, $order = NULL) {

        echo $this->input->get('segment'); //just for debug
        $tourevent_id = $this->input->get('segment');
        echo $tourevent_id; //just for debug

//        $where = array('tm_Course.tourevent_id' => $this->input->get('segment')); //tried this first
        $where = array('tm_Course.tourevent_id' => $tourevent_id); //same result as above
        $this->db->where($where);

        $data = parent::list_items($limit, $offset, $col, $order);

        $this->debug_query();

        return $data;
    }

    function _common_query() {

        parent::_common_query(); // to do active and published
    }

    function form_fields($values = array()) {
        $fields = parent::form_fields();

        return $fields;
    }

}

class Course_model extends Base_module_record {
    
}

Lot of thanks for your time and comments!

DaxCax · March 2013

Looks like it's _list_items_query() that adds to the WHERE clause, but I haven't figured out why really in the if..else statement.

admin · March 2013

What if you change the filter to "tourevent_id" instead of "segment"?

DaxCax · March 2013

Yes! Tried that last night and works like a charm.

Thanks for advices and your time!

Howdy, Stranger!

Categories

Select/where - Query run twice?

Comments