list_items() won't return anything when I add a join

starsinmypockets

my model:

class Pcc_links_model extends Base_module_model { public $foreign_keys = array('category_id' => 'pcc_link_categories_model'); function __construct() { parent::__construct('pcc_links'); } function list_items($limit = NULL, $offset = NULL, $col = 'pcc_links.name', $order = 'asc') { $this->db->join('pcc_link_categories', 'pcc_links.category_id = pcc_link_categories.id', 'left'); $this->db->select('pcc_links.name AS title', FALSE); $data = parent::list_items($limit, $offset, $col, $order); echo $this->db->last_query(); return $data; } } class Pcc_link_model extends Base_module_record { }

echo $this->db->last_query(); returns: SELECT pcc_links.name AS title FROM (pcc_links) LEFT JOIN `pcc_link_categories` ON `pcc_links`.`category_id` = `pcc_link_categories`.`id` ORDER BY pcc_links.name asc

This query works as expected if I put it directly into mysql...
If I remove the JOIN the items are listed as expected.

The joined class contains nothing of interest:

class Pcc_link_categories_model extends Base_module_model { public $record_class = 'Pcc_link_category'; function __construct() { parent::__construct('pcc_link_categories'); } } class Pcc_link_category_model extends Base_module_record { }

and the schema:

pcc_links table +-------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | | | | | url | varchar(255) | NO | | | | | description | text | NO | | NULL | | | published | enum('yes','no') | NO | | yes | | | category_id | tinyint(3) unsigned | NO | | NULL | | +-------------+---------------------+------+-----+---------+----------------+ pcc_link_categories table: +-----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | | | | | published | enum('yes','no') | NO | | yes | | +-----------+---------------------+------+-----+---------+----------------+

Any help is greatly appreciated. I'm baffled.

Lance

This works for me:

Module config:

$config['modules']['pcc_links'] = array( 'module_name' => 'Pcc Links', 'model_name' => 'pcc_links_model', 'default_col' => 'title', 'table_headers' => array( 'id', 'title', #'name' un-comment if you want the category name in there ) );

Pcc_links_model::list_items()

function list_items($limit = NULL, $offset = NULL, $col = '', $order = 'asc') { $this->db->join('pcc_link_categories', 'pcc_links.category_id = pcc_link_categories.id', 'left'); $this->db->select('pcc_links.id, pcc_links.name AS title', FALSE); #$this->db->select('pcc_links.id, pcc_links.name AS title, pcc_link_categories.name', FALSE); un-comment if you want the category name in there $data = parent::list_items($limit, $offset, $col, $order); return $data; }

Categories model unchanged.

I added in a way to show the related category name, assuming that's why you want to join in the first place.

Also, make sure you're hitting the little refresh icon up there by the search to clear any filters that may be in place.