› ›
CRITICAL SECURITY VERSION UPDATE:
https://github.com/daylightstudio/FUEL-CMS/releases/tag/1.4.13

Models - filters based on joined field

pierlopierlo
edited June 2012 in Feature Requests
i've tried adding the join to both the list_items and _common_query methods but if I add a field resulting from a JOIN operation this will produce an error (saying the field doesn't exist on the table).
I don't know how filters are applied, but I thought they might be applied to either the returned entries of a list_items call or to a common_query result. 

// My model
public $filters = array( 'name', 'collection'); // where collection is the JOINed field

...

function list_items($limit = NULL, $offset = NULL, $col = 'id', $order = 'desc') {
	$this->db->join('collections', 'collections.id = products.collection_id', 'left');
        $this->db->select('products.id, collections.title as collection', FALSE);
        $data = parent::list_items($limit, $offset, $col, $order);
	return $data;
}

...

function _common_query(){
         $this->db->join('collections', 'collections.id = products.collection_id', 'left');
	 $this->db->select('products.id, collections.title as collection', FALSE);
	 parent::_common_query();
}
but when i search through the backend search field i get 

A Database Error Occurred

Error Number: 1054

Unknown column 'products.collection' in 'where clause'
Any clue?

Comments

  • adminadmin
    edited 9:27AM
    If you are using it for filtering the list view, then the _common_query won't apply. What does the query look like when you do $this->debug_query(); before the data is returned?
  • pierlopierlo
    edited 9:27AM
    so printing the debug_query() inside list_items before the final return shows: 

    SELECT products.id, collections.title as collection
    FROM (products)
    LEFT JOIN `collections` ON `collections`.`id` = `products`.`collection_id`
    ORDER BY id desc
  • adminadmin
    edited 9:27AM
    That query doesn't seem to be the one causing the problem (no where clause). Did you have the filter applied when you ran the debug statement?
  • pierlopierlo
    edited 9:27AM
    No because if i keep the filter on it doesn't even get there. debug_query() returns:
    'UPDATE `ci_sessions` SET `last_activity` = 1340380635, `....'

    (session query)

    and PHP errors as stated above...
  • adminadmin
    edited 9:27AM
    Sounds like we need to track down which method is causing that SQL error. To do that, I would check the fuel/modules/fuel/controllers/module.php in the list_items method and see if you can track down which method in there is throwing the error.
Sign In or Register to comment.