problems with list items - order by defaulting to 'name'

swampthang · March 2011

This was solved so I thought I'd post this here in case someone else is scratching their head.

I have a projects table with the following fields:


CREATE TABLE IF NOT EXISTS `projects` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(150) NOT NULL,
    `description` text NOT NULL,
    `manager_id` int(5) NOT NULL,
    `start_date` datetime NOT NULL COMMENT 'Enter the date on which the project is supposed to begin.',
    `due_date` datetime NOT NULL COMMENT 'Enter the date on which the project is due.',
    `end_date` datetime NOT NULL COMMENT 'Enter the date on which the project was completed.',
    `notes` text NOT NULL COMMENT 'Enter notes. Can be used as a project diary.',
    `active` enum('yes','no') NOT NULL DEFAULT 'no',
    `completed` enum('yes','no') NOT NULL DEFAULT 'no',
    PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

My projects_model.php file has this:


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

class Projects_model extends Base_module_model {

    public $required = array('title');
    //public $foreign_keys = array('manager_id' => 'cms_users_model');

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

    function list_items($limit = NULL, $offset = NULL, $col = 'due_date', $order = 'asc')
    {
        $this->db->join('fuel_users', 'projects.manager_id = fuel_users.id', 'left');
        $this->db->select('projects.id, projects.title as project_title, DATE_FORMAT(projects.due_date,"%m/%d/%Y") as due_date, fuel_users.user_name as assigned_to, projects.active, projects.completed', FALSE);
        $data = parent::list_items($limit, $offset, $col, $order);
        return $data;
    }
}

When I click the 'Projects' module link in the CMS nothing shows up in the list though there is one record in there assigned to the admin user. I checked my mysql_query.log file and this was the entry:


110308 11:24:49    31 Init DB   intrajasent
    31 Query    SET NAMES 'utf8' COLLATE 'utf8_general_ci'
    31 Query    SHOW COLUMNS FROM `projects`
    31 Query    SELECT projects.id, projects.title as project_title, DATE_FORMAT(projects.due_date, "%m/%d/%Y") as due_date, fuel_users.user_name as assigned_to, projects.active, projects.completed
FROM (projects)
LEFT JOIN `fuel_users` ON `projects`.`manager_id` = `fuel_users`.`id`
ORDER BY due_date asc
    21 Init DB  intrajasent
    21 Query    SET NAMES 'utf8' COLLATE 'utf8_general_ci'
    21 Query    SHOW COLUMNS FROM `projects`
    21 Query    SELECT projects.id, projects.title as project_title, DATE_FORMAT(projects.due_date, "%m/%d/%Y") as due_date, fuel_users.user_name as assigned_to, projects.active, projects.completed
FROM (projects)
LEFT JOIN `fuel_users` ON `projects`.`manager_id` = `fuel_users`.`id`
ORDER BY due_date asc
    21 Query    SELECT projects.id, projects.title as project_title, DATE_FORMAT(projects.due_date, "%m/%d/%Y") as due_date, fuel_users.user_name as assigned_to, projects.active, projects.completed
FROM (projects)
LEFT JOIN fuel_users ON projects.manager_id = fuel_users.id
ORDER BY name asc
LIMIT 25
    21 Query    ROLLBACK
    21 Query    SET AUTOCOMMIT=1

Notice it's running the query 3 times in the process and the first 2 times, the order by line says - ORDER BY due_date asc which is correct. However, the last time it defaults to ORDER BY name asc. There's not a column in the table called 'name.' Just prior to this, I had a problem with the tasks table not displaying anything in the list and it was doing the same thing only at that time, the projects table used 'name' instead of title. The tasks table uses 'name' for the name of the task. I figured that might have been a conflict so I changed projects.name to projects.title in the database and the query. Here's what that looks like in my list_items function:


function list_items($limit = NULL, $offset = NULL, $col = 'tasks.due_date', $order = 'asc')
{
    $this->db->join('projects', 'projects.id = tasks.project_id', 'left');
    $this->db->select('tasks.id, DATE_FORMAT(tasks.due_date,"%m/%d/%Y") as due_date, projects.title as project_title, tasks.name as task_name, tasks.completed', FALSE);
    $data = parent::list_items($limit, $offset, $col, $order);
    return $data;
}

Changing the 'name' field in projects to 'title' fixed the problem of nothing showing up but it still automatically orders by name in that 3rd query. Here's the log:


110308 11:45:24    39 Init DB   intrajasent
    39 Query    SET NAMES 'utf8' COLLATE 'utf8_general_ci'
    39 Query    SHOW COLUMNS FROM `tasks`
    39 Query    SELECT tasks.id, DATE_FORMAT(tasks.due_date, "%m/%d/%Y") as due_date, projects.title as project_title, tasks.name as task_name, tasks.completed
FROM (tasks)
LEFT JOIN `projects` ON `projects`.`id` = `tasks`.`project_id`
ORDER BY tasks.due_date asc
110308 11:45:25    27 Init DB   intrajasent
    27 Query    SET NAMES 'utf8' COLLATE 'utf8_general_ci'
    27 Query    SHOW COLUMNS FROM `tasks`
    27 Query    SELECT tasks.id, DATE_FORMAT(tasks.due_date, "%m/%d/%Y") as due_date, projects.title as project_title, tasks.name as task_name, tasks.completed
FROM (tasks)
LEFT JOIN `projects` ON `projects`.`id` = `tasks`.`project_id`
ORDER BY tasks.due_date asc
    27 Query    SELECT tasks.id, DATE_FORMAT(tasks.due_date, "%m/%d/%Y") as due_date, projects.title as project_title, tasks.name as task_name, tasks.completed
FROM (tasks)
LEFT JOIN projects ON projects.id = tasks.project_id
ORDER BY name asc
LIMIT 25

I assume the problem of nothing showing up in the list is because there is no 'name' field in the projects table anymore, and it's trying to order by name.

swampthang · March 2011

I received a response from admin as follows:
Do you have a default_col property set for your module in your fuel/application/config/MY_fuel_modules.php file? Also, you may need to try logging out then back in because FUEL will save page state in a session including sorting parameters.
http://www.getfuelcms.com/user_guide/modules/simple

swampthang · March 2011

I logged out and back in and the problem of not seeing anything in the list was resolved. However, it was still ordering by the 2nd field in the table. I clicked the link above and discovered a list of properties for each module and their defaults. The default_col property is the 2nd field in the table but you can change it to be any field you like. I changed my module definition to the following and now it's sorting properly:


$config['modules']['projects'] = array(
    'module_name' => 'Projects',
    'model_name' => 'projects_model',
    'default_col' => 'due_date',
	'instructions' => 'Here you can manage your projects.'
);

jonnybojangles · August 2012

I had a similar issue while refactoring and it was corrected by logging out and back in. Could the session/sort cache be added to the docs(is this done through github)? Perhaps under the Simple Modules page?

admin · August 2012

You can definitely help improve the docs and would be much appreciated. I'd recommend doing it on the newer User Guide advanced module that will be used for 1.0 (currently in beta). Do you know how to fork and do pull requests? If not, here is some info:
https://help.github.com/articles/using-pull-requests

The 1.0 User Guide repo:
https://github.com/daylightstudio/FUEL-CMS-User-Guide-Module

Howdy, Stranger!

Categories

problems with list items - order by defaulting to 'name'

Comments