Filtering a list based on conditions

Question

The problem

I have a list of tasks with which a user is greeted with upon login. I want to be able to filter tasks according to assignment. The relevant fields on the table are:

**tasks table**
task_id   | (FK int) user  | (FK int) team
            (assigned to)    (assigned to)

And users belong to one team. On the front end I have checkboxes:

[ ] Mine     [ ] My Team's   [ ] Others    (Filter)

The conditions are mutually exclusive. For example, if I only check "My Team's", it's implied that I want to see all tasks assigned to my team but not me. If I check "Mine", I only want to see tasks assigned to me.

I can use any combination of the checkboxes to filter my results.

The (ugly) solution:

Convert flags as binary number to an int (little endian) and use switch:

$num = bindec("{$flag1}{$flag2}{$flag3}");

switch ($num) {
    // [ ] Mine   [ ] My Team's   [X] Others
    case 1:
        $filter = array('team <>' => $teamId);
        break;
    // [ ] Mine   [X] My Team's   [X] Others
    case 3:
        $filter = array('user <>' => $userId);
        break;

    /* a few more */

    // [X] Mine   [X] My Team's   [X] Others
    default:
        $filter = array();
        break;
}

The generated $filter array is passed on to a query builder.
It works. But I feel this is really ugly. Is there a better way?

Answer 1

Well, personally I don't think that's too ugly...

Why not just create two filter arrays (one for and, the other for or)

$andFilter = array();
$orFilter = array();
if ($flag1) {
    $orFilter['user = '] = $userId;
} else {
    $andFilter['user <> '] = $userId;
}
if ($flag2) {
    $orFilter['team = '] = $teamId;
} else {
    $andfilter['team <> '] = $teamId;
}
if ($flag3) {
    $orFilter['1 = '] = 1;
}

Then, join the queries as ($orFilter) AND ($andFilter) where each element of the filter is separated by the respective boolean operator (OR for $orFilter, AND for $andFilter)...

It should satisfy all your needs, and be relatively future proof (as you can add flags as you see fit)...

Answer 2

Sorry for the OCD/ADD nature I have, but I always have to go back to the real intention of the functionality. It seems hear that there may be some inconsistency in the model. Why do you have the team id in the tasks table? If a user can be on more than one team, than I understand why. But if a user is only on one team, then the team id in tasks can be derived from the relationship between the user and team. So by assigning the user to the task, you can in fact derive the team from the user.

That being said, this is how I would do it. First, add the filters to the form:

echo $this->Form->input('filter_user_id', array('type' => 'checkbox', 'label' => 'Mine'));
echo $this->Form->input('filter_team_id', array('type' => 'checkbox', 'label' => 'Team'));
echo $this->Form->input('filter_other', array('type' => 'checkbox', 'label' => 'Other')); 

Then in the controller function, check for the filters:

// process the filters
if($this->data['Task']['filter_user_id']) {
    $filter['Task.user_id'] = 'Task.user_id = ' . $user_id;
}
if($this->data['Task']['filter_team_id']) {
    $filter['Task.team_id'] = '(Task.team_id = ' . $team_id . ' AND Task.user_id <> ' . $user_id . ')';
}
if($this->data['Task']['filter_other']) {
    $filter['Task.id'] = '(Task.team_id <> ' . $team_id . ' AND Task.user_id <> ' . $user_id . ')';
}
$query = join(' or ', $filter);

$this->set('data', $this->Task->find('all', array('conditions' => array($query))));

This solution will work. You of course can adapt it for your needs.

NOTE: I updated the query to match what you requested. This will work more like you would expect. And while the queries may not be optimized, it still performs rather well.