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

jay_adhome · July 2013

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?

admin · July 2013

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.

jay_adhome · 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`

admin · July 2013

And what is the query when you use the key "fuel_relationships.foreign_key" ?

jay_adhome · July 2013

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

admin · July 2013

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.

jay_adhome · July 2013

Just pulled down the update, works great. Thanks for taking a look at it so quickly.

admin · July 2013

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.

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

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!

admin · December 2013

What if you use $this->input->get_post('campaign_id'); to look in the $_GET array instead of the $_POST

swampthang · 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

admin · December 2013

I wonder if the call to the parent is adding that:
$data = parent::list_items($limit, $offset, $col, $order);

swampthang · December 2013

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.

admin · December 2013

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.

Howdy, Stranger!

Categories

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

Comments