Allow querying by empty values on Advanced Query screen

[pkou <pkou at ua.fm>]

Currently, Query screen does not allow selecting empty columns. This is important for different types of fields:

• Lists like Version and Milestone;
• Edit boxes like Assigned to;
• Custom lists and edit boxes.

Proposed implementation is to use value (empty) for filtering by empty columns. For lists, value (empty) should be added as a first item. For edit boxes, it should be allowed to enter string (empty) in the edit box.

Patch attached.

[pkou <pkou at ua.fm>]

Patch for the changes

[pkou <pkou at ua.fm>]

Important notes about the patch:

• Hardcoded values are not used for fields status and resolution;
• Fields priority and severity are sorted properly;
• LEFT OUTER JOIN is used when tickets and custom fields are merged. Without it, any ticket that does not have a custom field (e.g. old tickets) will not be included in a query.

[Christopher Lenz]

I must say I'm not a big fan of the magic (empty) value. Clearly a solution to selecting empty properties would be cool, but I'd really prefer something more usable. And that needs to be worked out… I've been playing with ideas for a simpler but more powerful interface for building queries ever since I added the current rudimentary screen (see the comment on #203), and this particular requirement will be part of it.

Anyway, the other changes you mixed in with the ""select empty" enhancement look good, and I'll check those in in a sec. The one change that I don't think is a good idea is the ordering of enum rows by value. The default ordering of the enum rows actually makes a lot of sense and sorting alphabetically by value sort of destroys that "natural" order.

[Christopher Lenz]

Forget my concern about the ordering, I was confused. Ordering by the value component of course makes sense, and is not the same as alphanumeric ordering by the name. I'm going to check-in that part of the patch too.

In other news, I'm working on an overhauled query interface for 0.9 which will include the ability to filter by empty properties.

[pkou <pkou at ua.fm>]

0.9 seems not to be very soon but I am sure you have good ideas in mind for this issue.

No choice, I will wait till 0.9 for this issue :-)

[pkou <pkou at ua.fm>]

The most important in the patch is SQL statements that are used for querying empty values:

• Standard fields:

  SELECT id
  FROM ticket
  WHERE IFNULL(<ticket_field>, '') = ''
  

• Custom fields:

  SELECT id
  FROM ticket
  LEFT OUTER JOIN ticket_custom ON id = ticket
  WHERE name = '<custom-field-id>' AND IFNULL(value, '') = ''
        OR id NOT IN (SELECT ticket
                      FROM ticket_custom
                      WHERE name = '<custom-field-id>')
  

[Christopher Lenz]

I've added the ability to select by empty fields in the "better query" branch. You might want to check that out.

[Christopher Lenz]

New query branch has been merged into trunk in [1162]. It provides this functionality.