Need Help with certain JQL operators and functions

[f***@atlassian.com]

As Jira User who needs to use Advanced Search facility very extensively I need to know if the following is possible with JQL:


Can the following fields be supported and used as query parameters?:
-Start Date of Affects Version
-End Date of Affects Version
-Start Date of Fixed Version
-End Date of Fixed Version

issueHistory()
linkedIssues("")
votedIssues()
watchedIssues()

Can the following standard QL operations be supported by JQL:
-Using 'Count' function on records returned by a search
-Using 'nested select' function to use search results of inner query as parameters for the outer query
-Displaying search results for individual (selected) columns, not the entire data grid. Along with mathematical operators, it can help to automatically calculate many types of metrics (done manually today)
-Mathematical functions performed on data types that are integer:"-", "+", "/", "*"
--
Post by ggendel - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=39918

[f***@atlassian.com]

I don't think you're quite understanding what JQL is for. It has been added to address weaknesses in the existing search facilities (v3 downwards). It provides a way to return a list of issues in the same way as the old filters, but based on more clever searches.

It is *not* there to *report* on the results - that is done by the issue navigator, portlets, reports and so on. If you compare JQL to SQL, then you need to think of it as just the "where" clause, it returns all the issue data and relies on other parts of Jira to actually use that information.

So, to answer your five questions breifly
* dates of versions
* count function
* displaying columns
* calculated fields
All of these are irrelevant to JQL, they would be done in whatever you use to display the results of the filter. Nested select is the one I do not know about - I have only used simple queries so far, but I don't think it does support these (yet)

See http://confluence.atlassian.com/display/JIRA/Advanced+Searching for the list of things you can do.

However,
* "column display" is actually simple - write the JQL filter, save it and set the column order to whatever columns you need.
* For a count function, the dashboard gadgets and reports can summarise the results of filters in many ways, so it's quite likely that you can meet those with some gadgets (this may help with some of the calculations too).
* For more specific things like displaying dates of versions or calculating complex fields, I think you'll need to write gadgets or reports that can do these displays for you.
--
Post by broughn2 - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=39918

[f***@atlassian.com]

Thanks for your reply. I understand that JQL is not the same as SQL and most of the points that you addressed make sense. I will think of some work arounds.

One question remains though, perhaps I can rephrase it: to make sure our teams adhere to the process we are implementing I need to execute JQL queries that return results of JIRA issues that are in 'violation' of our processes.
For example, we work in iterations (time increments) of 2 weeks/14 days, where the last day of an iteration is essentially our 'Fix Version End Date'. So, I would love to know 48 hours before that date which issues are still...lets say.. in 'to do' status (were not worked on). So, I was hoping that I could include the following condition in my search parameters:
.....AND status = "new" AND 'Fix Version End Date' = now() + "2d" ....

Also, just so you understand what I meant by mathematical operators...I would like to do something like this:
project = XYZ and "Start Date" - "Date of First Response" > 3 AND......

If this is not possible by using JQL and somehow possible with reports, please advice.
Thanks in advance,
Gene Gendel
--
Post by ggendel - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=39918

[f***@atlassian.com]

.....AND status = "new" AND 'Fix Version End Date' = now() + "2d" ....

I can see what you mean - that sort of thing would be very useful, and it would be nice if JQL could include that in the searching (I hoped it was going to include a date + offset function of some sort)

Post by f***@atlassian.com
If this is not possible by using JQL and somehow possible with reports, please advice.

I don't think it is possible in JQL. Or the built-in reports either - I think you'll need to write something to do it.

A custom built report would work (you can include whatever you need if you're writing one), but I have another idea. Having never really written reports (and never for v4), I've often cheated and written "derived" custom fields that allow me to construct advanced searches. It's a trick, but it's sometimes appropriate, and I've found the code a lot easier.

One I wrote for a client ages ago (for Jira 3.4 if memory serves) accepted two date fields and calculated the difference. Using that woiuld do the trick nicely, because a user could enter "Start Date", the workflow would pick up "Date of first response" and the field would automatically display the difference, which can then be searched, and combined with "project = xyz"...
--
Post by broughn2 - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=39918

[f***@atlassian.com]

I think this got the information I needed. Thanks.
--
Post by ggendel - online at:
http://forums.atlassian.com/thread.jspa?forumID=46&threadID=39918