Filter in a simple module using a join in list items.

I've had good success with filters in a simple module as long as the field I am filtering on is in that model's table.

If I am doing a join in my list_items function, I haven't been able to find a way to make a filter drop down using a field from the joined table. Neither of the following have been successful for me.

$filters['foreign_key'] = array(
	'label' => 'Category', 
	'type' => 'select', 
	'options' => $this->categories_model->options_list(),
	'first_option' => 'Show All'
);	

$filters['fuel_relationships.foreign_key'] = array(
	'label' => 'Category', 
	'type' => 'select', 
	'options' => $this->categories_model->options_list(),
	'first_option' => 'Show All'
);	
If I don't add the table name it tries to do the where clause using the models table, if I do add the field name no error is thrown but it refreshes the page with nothing filtered (all results returned).

Any thoughts?

Comments

  • edited 5:45PM
    A couple questions:

    1. What version of FUEL?
    2. Can you output the query by perhaps adding the following item at the end of your model::list_items method and see what the query result is?
    $this->debug_query();

    Also, more as an FYI and may not be something that pertains to this situation, in 1.0, you can provide a "filter" method on your model to do custom filtering. The method will be automatically passed the models filters and should return an array of filters that you've further processed.
  • edited July 2013
    Version 1.0

    My filters code:
    
    function filters($filters = array()){
    	$filters['status_id'] = array(
    		'label' => 'Status', 
    		'type' => 'select', 
    		'options' => $this->status_model->options_list(),
    		'first_option' => 'Show All'
    	);
    	$filters['foreign_key'] = array(
    		'label' => 'Category', 
    		'type' => 'select', 
    		'options' => $this->categories_model->options_list(),
    		'first_option' => 'Show All'
    	);	
    
    	return $filters;
     } 	
    
    Error message:
    
    Unknown column 'fuel_experiences.foreign_key' in 'where clause'
    
    SELECT fuel_experiences.id, fuel_experiences.title, fuel_experiences.city, fuel_experiences.website, fuel_experiences.created FROM (`fuel_experiences`) LEFT JOIN `fuel_relationships` ON `fuel_relationships`.`candidate_key` = `fuel_experiences`.`id` LEFT JOIN `fuel_experiences_dates` ON `fuel_experiences_dates`.`experience_id` = `fuel_experiences`.`id` WHERE (LOWER(fuel_experiences.foreign_key) LIKE "%43%") GROUP BY `fuel_experiences`.`id`
    
    
  • edited 5:45PM
    And what is the query when you use the key "fuel_relationships.foreign_key" ?
  • edited 5:45PM
    No errors, but the filter doesn't work, returns everything. The query is below.
    
    SELECT fuel_experiences.id, fuel_experiences.title, fuel_experiences.city, fuel_experiences.website, fuel_experiences.created FROM (`fuel_experiences`) LEFT JOIN `fuel_relationships` ON `fuel_relationships`.`candidate_key` = `fuel_experiences`.`id` LEFT JOIN `fuel_experiences_dates` ON `fuel_experiences_dates`.`experience_id` = `fuel_experiences`.`id` GROUP BY `fuel_experiences`.`id` ORDER BY `slug` asc LIMIT 50 
    
  • edited 5:45PM
    I think I see the issue. It's because PHP transforms periods to underscores automatically in $_GET.
    http://stackoverflow.com/questions/68651/get-php-to-stop-replacing-characters-in-get-or-post-arrays

    I've posted an updated that will allow for the use of colons ":" instead of periods to separate.
  • edited 5:45PM
    Just pulled down the update, works great. Thanks for taking a look at it so quickly.
  • edited 5:45PM
    The fix actually created a regression bug that would occur when using the blog_comments module. This has been fixed now in the latest FUEL 1.0 commit.
  • edited December 2013
    I'm having a similar issue and I bet it's because I don't completely understand the list_items function. I wish there were a brief way to explain this. Will do my best...

    I have a games_model, players_model, plays_model and a campaigns_model that are all related in various ways. Simply put - 'players' 'play' 'games' that belong to a promotional 'campaign'.

    I'm using the players_model to list distinct players and their information in the CMS. I have a filters method as well. Here's a screenshot of the tables mentioned above.

    image

    My players_model contains the standard list_items method but I have filters that should determine what gets included in the list. There's a signup_date field that has a start and end date filter in an advanced search display and it works like a charm. However, I have another filter for getting only players info that have played a game belonging to a particular campaign. I can't figure out how to get that to work. I'm looking for the $_POST vars in the list_items method and running methods created to create the db->where array accordingly. As I said, the date filters work but not the campaign filter. In fact, it doesn't seem to ever reach the campaign_filtered_results() method. Instead, I get this error...

    Error Number: 1054 Unknown column 'players.campaign_id' in 'where clause' SELECT DISTINCT players.id, players.signup_date, players.email, players.status, players.power_play_balance, players.opt_in, players_to_clients.exported FROM (`players_to_clients`, `clients`, `players`) WHERE clients.id = 1 AND players_to_clients.client_id = 1 AND players_to_clients.player_id = players.id AND (players.campaign_id=1) Filename: /Applications/MAMP/htdocs/mpgsweeps/fuel/modules/fuel/models/base_module_model.php Line Number: 291
    Notice the query. In campaign_filtered_results() the select line is...
    $this->db->select('DISTINCT players.email, players.first_name, players.last_name, players.email, players.status, plays.campaign_id', FALSE);
    So, it's looking for campaign_id in the players model rather than the plays model.

    What the heck am I doing wrong?

    Here's the players model...

    class Players_model extends Base_module_model { public $required = array('first_name','last_name', 'email', 'password'); public $filters = array('first_name', 'last_name', 'email'); public $foreign_keys = array('player_id' => 'plays_model'); function __construct() { parent::__construct('players'); //table } function list_items($limit = NULL, $offset = NULL, $col = 'signup_date', $order = 'desc') { $CI =& get_instance(); $user = $CI->fuel_auth->user_data(); if ( $user['super_admin'] == 'no' ) { $this->load->model('clients_model'); $this->load->model('players_to_clients_model'); $this->load->model('plays_model'); $client_id = find_client_id(); if(isset($_POST['campaign_id'])) { $this->filter_join = array('campaign_id' => 'and'); $where = $this->campaign_filtered_results(); } elseif(isset($_POST['signup_date_fromequal'])) { $this->filter_join = array('signup_date_fromequal' => 'and'); $where = $this->date_filtered_results($client_id); } else { $where = $this->client_filtered_results($client_id); } $this->db->where($where, NULL, FALSE); } else { $this->db->select('DISTINCT players.id, players.first_name, players.last_name, players.email, players.status, players.power_play_balance', FALSE); } $data = parent::list_items($limit, $offset, $col, $order); return $data; } function campaign_filtered_results() { $this->db->select('DISTINCT players.email, players.first_name, players.last_name, players.email, players.status, plays.campaign_id', FALSE); $this->db->from('plays'); if(!empty($this->filters['campaign_id'])) { $campaign_id = $this->filters['campaign_id']; $where['plays.campaign_id = '] = $campaign_id; } elseif(isset($_POST['campaign_id'])) { $campaign_id = $_POST['campaign_id']; $where['plays.campaign_id = '] = $campaign_id; } $where = array( 'players.id' => 'plays.player_id', 'plays.campaign_id' => $campaign_id ); return $where; } function client_filtered_results($client_id) { $this->db->select("DISTINCT players.id, players.signup_date, players.email, players.status, players.power_play_balance, players.opt_in, players_to_clients.exported", FALSE); $this->db->from("players_to_clients,clients"); $where = array( 'clients.id' => (int)($client_id), 'players_to_clients.client_id' => (int)($client_id), 'players_to_clients.player_id' => 'players.id' ); return $where; } function date_filtered_results($client_id) { $this->db->select("DISTINCT players.id, players.signup_date, players.email, players.status, players.power_play_balance, players.opt_in, players_to_clients.exported", FALSE); $this->db->from("players_to_clients,clients"); $where = array( 'clients.id' => (int)($client_id), 'players_to_clients.client_id' => (int)($client_id), 'players_to_clients.player_id' => 'players.id' ); if(!empty($this->filters['signup_date_fromequal'])) { $signupfrom = $this->filters['signup_date_fromequal']; $signupto = $this->filters['signup_date_toequal']; $where['signup_date >= '] = "'".$signupfrom."'"; $where['signup_date <= '] = "'".$signupto."'"; } elseif(isset($_POST['signup_date_fromequal'])) { $datefrom = $_POST['signup_date_fromequal']; $dateto = $_POST['signup_date_toequal']; // will be in mm/dd/yyyy format. need to convert $signupfrom = $this->changeto_sqldate($datefrom); $signupto = $this->changeto_sqldate($dateto); $where['signup_date >= '] = "'".$signupfrom."'"; $where['signup_date <= '] = "'".$signupto."'"; } return $where; } // more stuff after this...
    Here's the filters method...
    function filters(){ $filters['signup_date_fromequal'] = array('type' => 'datetime'); $filters['signup_date_toequal'] = array('type' => 'datetime'); $client_id = find_client_id(); $campaigns = $this->get_campaigns($client_id); if(!empty($campaigns)) { foreach ($campaigns as $campaign) { $options[$campaign['id']] = $campaign['name']; } $filters['campaign_id'] = array('label' => 'Filter by campaign', 'type' => 'select', 'options' => $options, 'first_option' => 'Select campaign...'); } return $filters; }
    Thanks!
  • edited 5:45PM
    What if you use $this->input->get_post('campaign_id'); to look in the $_GET array instead of the $_POST
  • edited December 2013
    hmm. Ok. I changed the list_items method to:
    function list_items($limit = NULL, $offset = NULL, $col = 'signup_date', $order = 'desc') { $CI =& get_instance(); $user = $CI->fuel_auth->user_data(); if ( $user['super_admin'] == 'no' ) { $this->load->model('clients_model'); $this->load->model('players_to_clients_model'); $this->load->model('plays_model'); $client_id = find_client_id(); $campaign_id = $this->input->get_post('campaign_id'); if($campaign_id != null) { // $this->filter_join = array('campaign_id' => 'and'); $where = $this->campaign_filtered_results($campaign_id); } elseif(isset($_POST['signup_date_fromequal'])) { $this->filter_join = array('signup_date_fromequal' => 'and'); $where = $this->date_filtered_results($client_id); } else { // echo "cid: ".$campaign_id; $where = $this->client_filtered_results($client_id); } $this->db->where($where, NULL, FALSE); } else { $this->db->select('DISTINCT players.id, players.first_name, players.last_name, players.email, players.status, players.power_play_balance', FALSE); } $data = parent::list_items($limit, $offset, $col, $order); return $data; }and the campaign_filtered_results method to...
    function campaign_filtered_results($campaign_id) { $this->db->select('DISTINCT players.email, players.first_name, players.last_name, players.email, players.status, plays.campaign_id', FALSE); $this->db->from('plays'); $where = array( 'players.id' => 'plays.player_id', 'plays.campaign_id' => (int)($campaign_id) ); return $where; }Now the error is:Error Number: 1054 Unknown column 'plays.campaign_id' in 'field list' SELECT DISTINCT players.email, players.first_name, players.last_name, players.email, players.status, plays.campaign_id FROM (`plays`,`players`) WHERE players.id = plays.player_id AND plays.campaign_id = 1 AND (players.campaign_id=1) Filename: /Applications/MAMP/htdocs/mpgsweeps/fuel/modules/fuel/models/base_module_model.php Line Number: 291
    It looks like no matter what, the query gets written with players.campaign_id in it. If the last part of the above query is removed...

    AND (players.campaign_id=1)

    The query works perfectly. Trying to figure out what's forcing that last part onto the end of the query
  • edited 5:45PM
    I wonder if the call to the parent is adding that:
    $data = parent::list_items($limit, $offset, $col, $order);
  • edited 5:45PM
    It sure seems to be the case. I just can't figure out where that's happening. If I call it from outside the list_items method, like players_model->list_items() it doesn't do that.
  • edited 5:45PM
    It's probably being added in the base_module_model::_list_items_query method that is used in the list_items method. This method looks at the model's filters property to create certain where conditions. I'd poke around in that method.
Sign In or Register to comment.