Filter in comma separated values

Ron commented on Aug 8, 2021

Hello,

I have a column in the database that contains a list of ids with a comma separated values, ex. column teacher_id=1,5,19,22,89.

when I run the report, one of the params I transfer to the report if teacher_id. I want to be able to Filter as follow:

array("teacher_id", "=", $query_params[":teacher_id"]), "or", array($query_params[":teacher_id"], "contain", "teacher_id")

what I want to achieve in the line array($query_params[":teacher_id"], "contain", "teacher_id") is in case the teacher id = 19 and the column in the database is "1,5,19,22,89" it will return this row also in the filter.

Ron commented on Aug 8, 2021

I looked into the Filter process class and I added the following code which helps me. I think it will also help other that want to use MySQL FIND_IN_SET like function in the Filter process.

Case "findInSet":
    /*condition = array(3) {
        [0]=> string(10) "column"
        [1]=> string(9) "findInSet"
        [2]=> string(1) "value"
     }*/
    if (strpos(strtolower($value),",") > 0) {
        $isFiltered = in_array($condition[2],explode(",",$value));
    } else {
        $isFiltered = false;
    }
break;

What do you think. is there a better way to do it?

Sebastian Morales commented on Aug 9, 2021

Your method and implementation are good ways to solve the problem. Rgds,

Ron commented on Aug 9, 2021

The question is what will happen when I upgrade the the koolreport core folder. I will always need to remember that I made changes in the Filter.php

Sebastian Morales commented on Aug 10, 2021

Ok, you could use the Map process for filtering as well:

->pipe(new Map(array(
    "{value}" => function($row) {
        $value = $row["myColumn"]; //change "myColumn" to your column name
        if (strpos(strtolower($value),",") > 0) {
            $isFiltered = in_array($condition[2],explode(",",$value));
        } else {
            $isFiltered = false;
        }
        if ($isFiltered) return $row;
        else return null;
    }
)))

Ron commented on Aug 11, 2021

tnx

KoolReport's Forum

Filter in comma separated values #2249

Build Your Excellent Data Report