Note the upgraded forum! If you are experiencing issues logging in, you may need to reset your password which should send an email. If the email doesn't arrive, be sure to check your spam folder just in case.

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 4:30PM
    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 4:30PM
    And what is the query when you use the key "fuel_relationships.foreign_key" ?
  • edited 4:30PM
    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 4:30PM
    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 4:30PM
    Just pulled down the update, works great. Thanks for taking a look at it so quickly.
  • edited 4:30PM
    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 4:30PM
    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 4:30PM
    I wonder if the call to the parent is adding that:
    $data = parent::list_items($limit, $offset, $col, $order);
  • edited 4:30PM
    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 4:30PM
    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.