Edgewall Software
Modify

Opened 15 years ago

Closed 15 years ago

#879 closed defect (fixed)

Allow querying by empty values on Advanced Query screen

Reported by: pkou <pkou at ua.fm> Owned by: Christopher Lenz
Priority: normal Milestone: 0.9
Component: ticket system Version: devel
Severity: normal Keywords: query
Cc: Branch:
Release Notes:
API Changes:

Description

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.

Attachments (1)

patch-querynull-r1014.diff (5.0 KB ) - added by pkou <pkou at ua.fm> 15 years ago.
Patch for the changes

Download all attachments as: .zip

Change History (8)

by pkou <pkou at ua.fm>, 15 years ago

Attachment: patch-querynull-r1014.diff added

Patch for the changes

comment:1 by pkou <pkou at ua.fm>, 15 years ago

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.

comment:2 by Christopher Lenz, 15 years ago

Component: report systemticket system
Keywords: query added
Owner: changed from daniel to Christopher Lenz
Status: newassigned

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.

comment:3 by Christopher Lenz, 15 years ago

Milestone: 0.9

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.

comment:4 by pkou <pkou at ua.fm>, 15 years ago

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

comment:5 by pkou <pkou at ua.fm>, 15 years ago

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

comment:6 by Christopher Lenz, 15 years ago

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

comment:7 by Christopher Lenz, 15 years ago

Resolution: fixed
Status: assignedclosed

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

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Christopher Lenz.
The resolution will be deleted. Next status will be 'reopened'.
to as closed The owner will be changed from Christopher Lenz to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.