Filter list view in CMS by date range

swampthang · December 2013

Is there a way to set up a simple module to show a date-range picker in the CMS list view and filter on a specific field? How about other fields?

I have a table with thousands of records with 5 different fields plus a date field in the list view. Next to the search field, I'd like to place a date-range picker that would filter the list as well as a field selector to limit the search field to apply to. I've got the Export button there and it would be great to then Export that filtered list.

Is there anything in place for 1.0 - or on the horizon - that would make quick work of that?

thanks!

admin · December 2013

You can add those date filters using the Form_builder array syntax in your MY_fuel_modules.php file under the "filters" key of your module config. You can add suffixes of "_from" ( >), "_fromequal (>=)", "_to (

function filters(){
   $filters['start_date_fromequal'] = array('type' => 'date');
   $filters['end_date_toequal'] = array('type' => 'date');
   return $filters;
}

There is also an "advanced_search" parameter you can specify in your module config. This will give you a drop down from the search box that will display the filter form fields and not make it so crowded up near the top.

On line 294 of the base_module_model, you can uncomment that line to see the generated SQL.

With regards to exporting the data, you can overwrite the "export_data" method on your model that's inherited from the base_module model (line 794) to do what you need.

swampthang · December 2013

Wow, thanks. There are a lot of options. So, I've tried various combinations of what you entered above.

Currently I have this in the plays_model right after foreign keys...

  public $filters = array('players.first_name', 'players.last_name', 'players.email', 'plays.winner', 'plays.time_played');

Then, down in the model I have this:

function filters(){
  $filters['time_played_fromequal'] = array('type' => 'datetime');
  $filters['time_played_toequal'] = array('type' => 'datetime');
  return $filters;
}

In My_fuel_modules.php I added 'advanced_search' => TRUE to the 'plays' module definition.

This results in the following dropdown screenshot(couldn't get the image to embed here for some reason):

https://dl.dropboxusercontent.com/u/7699254/Screenshots/fcms_dropdown_menu1.png

There are a couple of things that are making this a bit tough.

First, the datetime format in the database is yyyy:mm:dd 00:00:00 so the default format of the datetime form fields of mm/dd/yyyy isn't matching with the db. I've been trying to figure out how to change that.

Second, the date comparison defaults to OR so the WHERE part of the query is coming out like this:


WHERE plays.client_game_id = client_games.id AND client_games.client_id = 1 AND (plays.time_played >='04/01/2013' OR plays.time_played <='12/12/2013')

As you can see, if the date format matched what is in the db, that query would return everything - filtering nothing. As it is above, since the date format doesn't match, it returns an empty set. One other thing to note is that the time isn't being pulled in either.

The only place I've been able to enter a filter_join of AND to the datetime comparison that results in the WHERE part of the query being constructed correctly is in the filters method on one or both of the items like:

$filters['time_played_fromequal'] = array('type' => 'datetime', 'filter_join' => 'and');

but that results in the following error when the list page first loads...

A PHP Error was encountered
Severity: Notice

Message: Undefined index: time_played_fromequal

Filename: controllers/module.php

Line Number: 215

(I'm using php 5.4.19)

At any rate, if I go into phpMyAdmin and change the WHERE section to be...

WHERE plays.client_game_id = client_games.id AND client_games.client_id = 1 AND (plays.time_played <='2013/12/12 12:00:00') AND (plays.time_played >='2013/04/01 23:59:00')

It returns an accurate recordset.

What am I doing wrong? Is there a tut for this anywhere?

Thanks.

swampthang · December 2013

I just created a new branch locally and pulled and merged the latest files from 1.0 btw.

admin · December 2013

I just posted some changes to the master branch that should address some of these issues regarding using dates.

swampthang · December 2013

Thanks. I see you were up late last night working on this. I really appreciate your dedication to this CMS. Love the new look, btw!

So, I've pulled from your master branch and I bet I just don't know what I'm doing. (my money's on that anyway) I've tried all the same combinations of ways of achieving the date 'AND' filter but to no avail. I can get it to filter properly if I use the function filters() in my 'plays' model like this:

function filters(){
  $filters['time_played_fromequal'] = array('type' => 'datetime', 'filter_join' => 'and');
  $filters['time_played_toequal'] = array('type' => 'datetime');
  return $filters;
}

But if I do it that way I get the following error:

A PHP Error was encountered

Severity: Warning

Message: Illegal string offset 'time_played_toequal'

Filename: controllers/module.php

Line Number: 215

If I remove: 'filter_join' => 'and' the error goes away but the resulting query uses 'OR' between the dates searched. I've set up a repo and shared it with you on github if it would help explain better how I have things set up.

admin · December 2013

The filter join is something that needs to be set as a property of the model such as :
$this-> filter_join = 'and'; // will be used for all fields
$this-> filter_join = array('time_played_fromequal' => 'and'); // will be used for only the time_played_fromequal field

Also, there was a typo in the last commit and the minute field would show the month value. I've updated that.

swampthang · December 2013

Ahhh, thanks. I've changed it and it now works perfectly.

One more thing since we're talking about the list view in the CMS. I can't get the list view to initially order_by what I set in the list_items function. It seems to pick something to order by but I can't figure out what criteria it uses. I looked at other models and studied the types of fields, order of the fields, etc and couldn't find any pattern. In fact, none of my models' list_items ordering is working.

I completely removed the 'winner' field from db->select and still it ends up being ordered by that asc.

Here's what I have:

function list_items($limit = NULL, $offset = NULL, $col = 'time_played', $order = 'desc')
{
  $CI =& get_instance();
  $user = $CI->fuel_auth->user_data();
  $this-> filter_join = array('time_played_fromequal' => 'and');
  $this->db->join('client_games', 'client_games.id = plays.client_game_id', 'left');
  $this->db->join('players', 'players.id = plays.player_id', 'left' );
  $this->db->join('prizes', 'prizes.id = plays.prize_id', 'left');
  $this->db->join('power_prizes', 'power_prizes.id = plays.power_prize_id', 'left');
  $this->db->select("plays.id, players.first_name, players.last_name, players.email, client_games.new_game_name as 'game_played', prizes.prize_name, plays.time_played", FALSE);

  if ( $user['super_admin'] == 'no' ) {
  	$this->load->model('clients_model');
  	$this->load->model('client_games_model');
  	
  	$client = $this->clients_model->find_one_by_fuel_user_id($user['id']);
  	$client_id = $client['id'];
    
  	$this->db->where(
      array( 
        'plays.client_game_id' => 'client_games.id', 
        'client_games.client_id' => $client_id), NULL, FALSE
      );
  }

  $data = parent::list_items($limit, $offset, $col, $order);

  return $data;

}

you can see that the sort column should be 'time_played' but the query when the page initially loads shows - .... ORDER BY `winner` asc LIMIT 50

Here's the plays table:


Column                 Type                     Null
id                     int(15)                  No
player_id              int(10)                  No
client_game_id         int(9)                   No
prize_id               int(10)                  No
power_prize_id         int(3)                   No
winner                 enum('yes', 'no')        No
time_played            datetime                 No
entries                int(5)                   No
last_play_entry        datetime                 No
fb_share_score         tinyint(1)               No
fb_share_time          tinyint(1)               No
fb_share_game          tinyint(1)               No
twitter_share_game     tinyint(1)               No
email_share_game       tinyint(1)               No

swampthang · December 2013

I found that if I echo out the $col and $order variables I get:
$col - 'winner'
$order - 'asc'

The only way I can change that is to define the variables in the list_items function:

$col = 'time_played';
$order = 'desc';

Then it works. But, of course, then you can no longer click the column headings and sort on that column. I cannot figure out where the col and order vars are being set to winner and asc. I've searched my entire site for all instances of winner and haven't found anything that would cause that.

admin · December 2013

It could be something in the session. Try logging out. You can specify the a default_col and default_order for you module in the MY_fuel_modules.php file

swampthang · December 2013

Logging out and in (session) didn't have any effect. But, adding...

'default_col' => 'time_played',
'default_order' => 'desc'

...to the $config['modules']['plays'] array in My_fuel_modules.php did the trick. Thanks!

swampthang · December 2013

Why would this not set the $col and $order vars on any of my models? Am I the only one this is happening to?

function list_items($limit = NULL, $offset = NULL, $col = 'time_played', $order = 'desc')

admin · December 2013

It has to do with FUEL assigning the default sorting field as the display_field value which overwrites the functions default value.

Howdy, Stranger!

Categories

Filter list view in CMS by date range

Comments

A PHP Error was encountered