Front end views the query is not joining

ziggyz27 · March 2014

My module has joined query
For some reason my view is not able to query the joined table, the profiler shows the query is just using a single table without joining. When trying to pull all records for a category it simple says no such column.

admin · March 2014

Can you post some code for the query/model method.

ziggyz27 · March 2014

Sure this is the projects model that is being used for both backend and front

if (!defined('BASEPATH')) exit('No direct script access allowed');

require_once(FUEL_PATH . 'models/base_module_model.php');

class Projects_model extends Base_module_model {


    public $filters = array('description','heading'); // filters to apply to when searching for items
    public $filter_join = 'or'; // how to combine the filters in the query (and or or)
    public $required = array('project_category_id','heading','slug','description'); // an array of required fields. If a key => val is provided, the key is name of the field and the value is the error message to display
    public $foreign_keys = array('project_category_id' => array(FUEL_FOLDER => 'project_categories_model'));
    public $linked_fields = array(); // fields that are linked meaning one value helps to determine another. Key is the field, value is a function name to transform it. (e.g. array('slug' => 'title'), or array('slug' => arry('name' => 'strtolower')));
    public $boolean_fields = array(); // fields that are tinyint and should be treated as boolean
    public $unique_fields = array('slug'); // fields that are not IDs but are unique. Can also be an array of arrays for compound keys
    public $parsed_fields = array(); // fields to automatically parse
    public $serialized_fields = array(); // fields that contain serialized data. This will automatically serialize before saving and unserialize data upon retrieving
    public $belongs_to = array(); // keys are model, which can be a key value pair with the key being the module and the value being the model, module (if not specified in model parameter), relationships_model, foreign_key, candidate_key
    public $formatters = array(); // an array of helper formatter functions related to a specific field type (e.g. string, datetime, number), or name (e.g. title, content) that can augment field results
    public $display_unpublished_if_logged_in = FALSE;
    
    protected $friendly_name = ''; // a friendlier name of the group of objects
    protected $singular_name = ''; // a friendly singular name of the object
    
    function __construct()
    {
        parent::__construct('my_projects'); // table name
    }

    function list_items($limit = NULL, $offset = NULL, $col = 'heading', $order = 'asc', $just_count = FALSE)
    {
        $this->db->join('my_project_categories', 'my_project_categories.id = my_projects.project_category_id', 'left');
        $this->db->select('my_projects.id, CONCAT(my_projects.heading, "-", my_project_categories.category) as heading, slug, my_project_categories.category as category, SUBSTRING(description, 1, 90) AS description, showcase_project, published', FALSE);
        $data = parent::list_items($limit, $offset, $col, $order, $just_count);
 
        // check just_count is FALSE or else $data may not be a valid array
        if (empty($just_count))
        {
          foreach($data as $key => $val)
          {
            $data[$key]['description'] = htmlentities($val['description'], ENT_QUOTES, 'UTF-8');
          }
        }
        return $data;
    }
    
    function form_fields($values = array(), $related = array())
    {   
        $fields = parent::form_fields($values, $related);

        // ******************* ADD CUSTOM FORM STUFF HERE ******************* 
        $fields['description']['img_folder'] = 'projects/';
        $fields['image']['folder'] = 'images/projects/';

        return $fields;
    }

    function tree()
    {
        return $this->_tree('foreign_keys');
    }
}
 
class Project_model extends Base_module_record
{
    public function get_url()
    {
        return site_url('projects/'.$this->slug);
    }

    public function get_image_path()
    {
        return img_path('images/projects/'.$this->image);
    }

    public function get_thumb_image_path()
    {
        return img_path('images/projects/'.$this->thumb_image);
    }
}

And the query its running without join when filtered by category is this:


SELECT `my_projects`.* FROM (`my_projects`) WHERE `my_project_categories`.`category` = 'XYZ' AND `my_projects`.`published` = 'yes' LIMIT 10

NOTE: I am using my own categories module.

Whats confusing is that it displays all records just fine when not filtering by category and even pulls the category for each record correctly. But the query that profiler shows for all projects view is this, note that there is no categories joined - hows it pulling the correct category:


SELECT `my_projects`.* FROM (`my_projects`) WHERE `my_projects`.`published` = 'yes' LIMIT 10

The view is simply as follows:


$slug =  urldecode(uri_segment(2));
$offset = '';
$CI =& get_instance();
$CI->load->library('pagination');
$CI->load->model('projects_model');
        $list_where = array('category' => $slug);
        $model = 'projects_model';
        $limit = 10;
        $total = $CI->projects_model->record_count();
        $projects = $CI->projects_model->find_all($list_where, '', $limit, $offset);

foreach($projects as $project) :

echo $project->heading . ' - ' .$project->project_category['category']; 

echo $project->description;  

endforeach;

admin · March 2014

Your model only contains a join in the list_items method and not one set for the find_all method. To add it to the find_* methods you can add the join in the model's _common_query() method:

function _common_query(){
     parent::_common_query();
     $this->db->select('my_projects.id, CONCAT(my_projects.heading, "-", my_project_categories.category) as heading, slug, my_project_categories.category as category, SUBSTRING(description, 1, 90) AS description, showcase_project, published', FALSE);
     $this->db->join('my_project_categories', 'my_project_categories.id = my_projects.project_category_id', 'left');
}

ziggyz27 · March 2014

oh.. just when I though there wasnt more to it... had no clue about _common_query()'s details. Maybe the same example should be added to the documentation.

This makes it even more powerful. Thanks I have more control now. It works.

Howdy, Stranger!

Categories

Front end views the query is not joining

Comments