JQL filter for open issues in all projects a user has worked on

Question

Hi there, I need help from a real expert in regards to Jira JQL filtering and/or ScriptRunner Extended Search.

What I am looking for is basically a filter that shows results based on:

* currently logged in user

* the projects he/she usually works on

* issues in "To Do" status category

I know how to find the projects like e.g. assignee = currentUser() OR worklogAuthor = currentUser() OR commentedBy = currentUser().

I, of course, also know how to use statusCategory IN ("To Do") or similar.

Now, I am looking for a way to do something with a sub query like:

statusCategory IN ("To Do") AND project IN MyDesiredSubQuery(assignee = currentUser() OR worklogAuthor = currentUser() OR commentedBy = currentUser())

So far I've tried several ways to do it in native JQL and also with ScriptRunner Extended Search using projectMatch() but nothing works.

I am aware that JQL is not intended to deliver results for projects but for issues. Still I want to limit the number of results to just the projects the user usually works on.

In the end, I want to have ONE filter for ONE dashboard that works for all users in the organization and shows open and unhandled issues just for the projects they usually contribute to - not other projects.

How could that be done?

EDIT:

Thank you all for your answers.

projectsWhereUserHasRole() was the key. This actually works for me now:

(
    project IN projectsWhereUserHasRole("Member") OR 
    project IN projectsWhereUserHasRole("Administrator") OR 
    project IN projectsWhereUserHasRole("Service Desk Team") OR 
    project IN projectsWhereUserHasRole("Internal")
) AND
  statusCategory IN ("To Do") AND 
  status IN (New, "To Do") AND 
  assignee IS EMPTY

Thomas Gericke_krw0yj · Answer

This returns only tickets the user is assignee of. That's exactly not what I want. I want to find the tickets that are actually open/unhandled/without assignee.

Yeah, I know. My original post was not clear about that. Sorry.

Duc Thang TRAN · Answer

Hello @Thomas Gericke

After reading your description,
I think of a JQL like this:
statusCategory IN ("To Do") AND assignee = currentUser() AND (worklogAuthor = currentUser() OR commentedBy = currentUser())

That way, it will retrieve all issues with this value.
Then, create a two-dimension gadget, with the X-axis as Assignee (or Issue Type if you want one more level of breakdown) and the Y-axis as Project.
That way, you will have each line by project, showing the number of tickets or  where time was logged or comments were added

Hope this can help

Marc -Devoteam- · Answer

Hi @Thomas Gericke

Create filters with dynamic date periods, like endOfWeek, startOfWeek, etc...

https://support.atlassian.com/jira-software-cloud/docs/jql-functions/

There are no work area option in JQL not even in apps that can extend JQL, projects are fixed options unfortunate.