Hello, I had the same problem, that seems to be there around since a long time (example: https://forum.osticket.com/d/93534-quick-filter-not-working-with-complex-indexes). In fact, I updated from 1.14 to 1.18.1 because of this bug, hoping the problem would be solved.
Unfortunately it wasn't the case, so I digged into the code and found a workaround.
The problem in the SQL request is that a term of the WHERE expression is missing, involving a doubled 'AND':
SELECT ... WHERE A2.state = 'open'AND AND((A2.state = 'open' AND (A1.staff_id = 1 OR A5.staff_id = 1 OR A6.object_type = 'C' AND A8.staff_id = 1 OR A1.team_id IN (1, 3, 4, 5, 6, 7, 9) OR B0.team_id IN (1, 3, 4, 5, 6, 7, 9) OR A6.object_type = 'C' AND B1.team_id IN (1, 3, 4, 5, 6, 7, 9))) OR A1.dept_id IN (4, 5, 6, 7, 8) OR B2.id IN (4, 5, 6, 7, 8) OR A6.object_type = 'C' AND B3.id IN (4, 5, 6, 7, 8)) AND (A1.ticket_pid IS NULL OR A1.flags & 8 != 0) GROUP BY A1.ticket_id ORDER BY B5.priority_urgency ASC, A1.lastupdate DESC LIMIT 50
Normally, between the two "AND AND" should be the condition based on the "filter=" request parameter. When using a choice field, the related values are stored in the ost_ticket__cdata table which has a specific field created for each choice ticket form fields. In my case, it's the field_38 that should be used for the condition.
For a filter value of "example_value", we should have the following condition:
AND ost_ticket__cdata.field_38 LIKE "%example_value%"
Here is the fix I crafted to allow this:
In ./include/class.forms.php is modified :
@@ -2121,6 +2121,8 @@ class ChoiceField extends FormField {
function applyQuickFilter($query, $qf_value, $name=false) {
global $thisstaff;
$field = new AssigneeChoiceField();
//special assignment quick filters
switch (true) {
+ case (substr($name, 0, 5) == 'cdata'):
+ return $query->filter((new FormField())->getSearchQ('contains', $qf_value, $name));;
case ($qf_value == 'assigned'):
case ($qf_value == '!assigned'):
$result = $field->getSearchQ($qf_value, $qf_value);
return $query->filter($result);
case (strpos($qf_value, 's') !== false):
case (strpos($qf_value, 't') !== false):
case ($qf_value == 'M'):
case ($qf_value == 'T'):
$value = array($qf_value => $qf_value);
$result = $field->getSearchQ('includes', $value);
return $query->filter($result);
break;
}
return $query->filter(array(
$name ?: $this->get('name') => $qf_value,
));
}
In fact, the switch cases are not able to handle the choices fields. I thus added an ugly condition checking the $name of the filter (in my case cdata__field38) to see if it begins with 'cdata' and if so create a specific condition term with the 'contains' keyword (that generates a LIKE '%...%' expression).
