Models - filters based on joined field

pierlo

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?

admin

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?

pierlo

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 

admin

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?

pierlo

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...

admin

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.