[SELECT A1.number AS number, A1.ticket_id AS ticket_id, A1.lastupdate AS lastupdate, B4.subject AS cdata__subject, A1.ticket_pid AS ticket_pid, A1.flags AS flags, A1.isoverdue AS isoverdue, B7.name AS user__cdata__name, A1.ip_address AS ip_address, B8.firstname AS staff__firstname, B8.lastname AS staff__lastname, C0.name AS team__name, A1.team_id AS team_id, A1.dept_id AS dept_id, C1.name AS dept__name, B4.priority AS cdata__priority, A1.isanswered = '0' AS 2GkBZxz, (SELECT COUNT(R1.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) LEFT JOIN ost_attachment R1 ON (R1.type = 'H' AND R0.id = R1.object_id) WHERE Q8.ticket_id = A1.ticket_id AND R1.inline = 0) AS _att_count, (SELECT COUNT(R0.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id AND NOT R0.flags & 4 != 0) AS _thread_count, (SELECT COUNT(R0.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_collaborator R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id) AS _collabs, A1.ip_address IS NOT NULL AS 9ev6ct0, COALESCE(B8.firstname, B8.lastname, C0.name, 'zzz') AS assignee FROM ost_ticket A1 JOIN ost_ticket_status A2 ON (A1.status_id = A2.id) LEFT JOIN ost_thread A3 ON (A3.object_type = 'T' AND A1.ticket_id = A3.object_id) LEFT JOIN ost_thread_referral A4 ON (A3.id = A4.thread_id) LEFT JOIN ost_staff A5 ON (A4.object_type = 'S' AND A4.object_id = A5.staff_id) LEFT JOIN ost_thread A6 ON (A1.ticket_id = A6.object_id AND A6.object_type = 'C') LEFT JOIN ost_thread_referral A7 ON (A6.id = A7.thread_id) LEFT JOIN ost_staff A8 ON (A7.object_type = 'S' AND A7.object_id = A8.staff_id) LEFT JOIN ost_team B0 ON (A4.object_type = 'E' AND A4.object_id = B0.team_id) LEFT JOIN ost_team B1 ON (A7.object_type = 'E' AND A7.object_id = B1.team_id) LEFT JOIN ost_department B2 ON (A4.object_type = 'D' AND A4.object_id = B2.id) LEFT JOIN ost_department B3 ON (A7.object_type = 'D' AND A7.object_id = B3.id) LEFT JOIN ost_ticket__cdata B4 ON (A1.ticket_id = B4.ticket_id) LEFT JOIN ost_ticket_priority B5 ON (B4.priority = B5.priority_id) LEFT JOIN ost_user B6 ON (A1.user_id = B6.id) LEFT JOIN ost_user__cdata B7 ON (B6.id = B7.user_id) LEFT JOIN ost_staff B8 ON (A1.staff_id = B8.staff_id) LEFT JOIN ost_team C0 ON (A1.team_id = C0.team_id) LEFT JOIN ost_department C1 ON (A1.dept_id = C1.id) 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]

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND ((A2.state = 'open' AND (A1.staff_id = 1 OR A5.staff_id = 1 OR A6....' at line 1<br />
<br />
---- Backtrace ----<br />
#0 C:\XAMPP\htdocs\include\mysqli.php(211): osTicket->logDBError('DB Error #1064', '[SELECT A1.num...')<br />
#1 C:\XAMPP\htdocs\include\class.orm.php(3482): db_query('SELECT A1.`numb...', true, true)<br />
#2 C:\XAMPP\htdocs\include\class.orm.php(3529): MySqlExecutor->execute()<br />
#3 C:\XAMPP\htdocs\include\class.orm.php(2104): MySqlExecutor->getArray()<br />
#4 C:\XAMPP\htdocs\include\class.orm.php(2054): HashArrayIterator->{closure}()<br />
#5 C:\XAMPP\htdocs\include\class.orm.php(2033): CallbackSimpleIterator->next()<br />
#6 C:\XAMPP\htdocs\include\class.orm.php(2042): CallbackSimpleIterator->rewind()<br />
#7 C:\XAMPP\htdocs\include\class.orm.php(1713): CallbackSimpleIterator->valid()<br />
#8 C:\XAMPP\htdocs\include\class.orm.php(1723): CachedResultSet->fillTo(9223372036854775807)<br />
#9 C:\XAMPP\htdocs\include\class.orm.php(1739): CachedResultSet->asArray()<br />
#10 C:\XAMPP\htdocs\include\staff\templates\queue-tickets.tmpl.php(266): CachedResultSet->getIterator()<br />
#11 C:\XAMPP\htdocs\scp\tickets.php(569): require_once('C:\XAMPP\htdocs...')<br />
#12 {main}

8 months later

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).

    bterradillos

    Thanks for sharing however I would not recommend your patch. I will add this to my list of bugs to look into.

    Cheers.

      Neither would I (recommending my patch) ;-) because it is too "hardcoded" to be stable over time and it bypass the logic of typed field-type specific queries.

      Concerning this bug, I wondered if quick filters with choice fields worked once? Or has it never been implemented specifically?

      Write a Reply...