Filters with a LIKE clause

marcinkonys · February 2016

I have a module where a number of entries with dates are added. I was meaning to add more filtering options so it would be easier for employees to search. I had no issue adding a filter used with the search field itself, just added an item referencing a column I wanted to search in the $filters array.

It would be ideal if I could search using the entries' dates, too, but just adding the date column gives me ALL the options. What I wanted was a dropdown with the distinct year entries from the referenced DATE type MySQL column.

I read the user guide on filtering, and it did touch on more advanced filters where you append "_to" or "_from" to the column name, however with dates this does not work easily.

What I ended up with was this:

$config['modules']['purchases'] = array(
	// ...
	'advanced_search' => TRUE,
	'filters' => array(
		'purchase_date' => array(
				'label' => 'Date',
				'type' => 'select',
				'model' => 'purchases',
				'model_params' => array('year(purchase_date)', 'year(purchase_date)'),
				'hide_if_one' => TRUE,
				'first_option' => 'Choose the year...',
		),
	)
);

This renders the filter correctly and I can see the dropdown with several year entries. However it does not actually filter the table.

I tried altering the list_items method, too, however it had no effect:

function list_items($limit = null, $offset = null, $col = 'purchase_date', $order = 'desc')
    {
        $year = $this->input->get('purchase_date');
        if (preg_match('/^2\d{3}$/', $year)) {
            $this->db->like('purchase_date', $year, 'after');
        }

        $this->db->select('purchase_id, purchase_date, purchase_items', FALSE);
        $data = parent::list_items($limit, $offset, $col, $order);
        return $data;
    }

Am I missing something simple here? I read a number of threads around here and saw people altering the list_items method like that, yet it seems it always renders everything on my side.

admin · February 2016

It may be helpful to debug what that actual query is. To do so, go to the fuel/modules/fuel/models/base_module_model.php around line 327 in the list_items method and uncomment the line that has:
//$this->debug_query();
Let me know if that sheds any more light on your issue.

marcinkonys · February 2016

Thanks for the reply, admin. I didn't notice that line before. Uncommented it and tried refreshing the page. It showed a SELECT without a WHERE clause.

What caught my attention, though, was that the friendly "Filters:" string next to the items showed a purchase_date of "1970-01-01 03:00:00".

I launched XDebug from PhpStorm and stepped around until finally finding the culprit.

In module.php, on line 618, it checks the type of the field referenced by each filter. For my filter, it's 'date', and since I'm only passing the year portion of it, the subsequent call to 'is_date_format' on line 622 returns 'false', and I end up with a default value instead.

I first overrode the field_type method in my model like so:

function field_type($field) {
    if ($field === "purchase_date") {
        return "year";
    }
    return parent::field_type($field);
}

Which made the filter behave exactly like I want (and I didn't even need the first four lines in my 'list_items' method I posted above any more. yay!).

This is not ideal though, as now 'auto_validate_field' in Form_builder thinks this date is a year, so I only get validation of the first 4 characters of it.

I ended up with the following:

function field_type($field) {
    if ($field === "purchase_date") {
        $is_item_view = $this->uri->segment(3) === "items";
        if ($is_item_view) {
            return "year";
        }
    }
    return parent::field_type($field);
}

Obviously not ideal still, but I think this should be fine? I think field_type is called only once while in the 'items' page, so this shouldn't inadvertently mess something up.

admin · February 2016

What about giving it a different name of "purchase_year" and then in your list_items method you check for that in the query string parameters:


public function list_items($limit = NULL, $offset = NULL, $col = 'completed', $order = 'desc', $just_count = FALSE)
{
	if (!$this->input->get('purchase_year'))
	{
		$this->db->where('YEAR(purchase_date, "%Y", $this->input->get('purchase_year'));
		unset($this->filters['purchase_year']);
	}
	//.... rest goes here

marcinkonys · February 2016

I swear I thought it gave me an error a couple of days ago when I named the filter something that was not the name of a column. Gahh! This is so simple after all.

Massive thanks

admin · February 2016

It may have... the unset is what takes care of that.

Howdy, Stranger!

Categories

Filters with a LIKE clause

Comments