[IM] logical operations with relationships in queries

Hi Jens,

We've run into this exact issue in the past as well. I believe we were added to an RFC, but I don't recall the number.

The workarounds we use are:

1) Query for the Tasks themselves, pulling the MR ID as the value in the TaskChild_to_ModulParent column.

a) This means that you will get duplicate MR IDs if there are multiple tasks meeting the query criteria, so you need to filter those out somehow in whatever you do with the query results

b) This also means that any fields located on the MR will not be available in the query result unless they are FVA'd or copied down to the TaskChild level somehow.

2) Write custom SQL against the DB backend so you can get the INNER JOINs exactly how you need them

a) This works well but does not meet your requirement of getting it in an IM query.

If neither of those works, you could go with a trigger approach. This is probably not very efficient or scalable, but it should get the job done.

1) Create a scheduled trigger which runs against an admin query of either the red or the green queries. As you mentioned, this will pull extra items that you don't want in the end result.

2) In that trigger script, loop over each of the item beans and check the other criteria. (So if you used the red query for the trigger, then check the state of each TaskChild, if you used the Green, then check the Type of each TaskChild)

3) If the item matches the criteria, then save the MR ID into an ArrayList or some other variable.

4) After processing all of the original query item beans, loop over the variable holding the MR IDs to generate a query definition of the form

"((field["ID"] = "X") OR (field["ID"] = "Y") OR (field["ID"] = "Z") OR ...)"

5) Use an im createquery/editquery API call to create/update a query with the new definition

If you needed to do this for multiple sets of queries, it might be better to not hardcode the logic and instead pass in some query names as parameters, execute those queries with im issues API calls and merge the resulting set of IDs. (Google has an open source java library called Guava which is great at this sort of thing. http://docs.guava-libraries.googlecode.com/git/javadoc/com/google/common/collect/Sets.html#intersection(java.util.Set, java.util.Set))

Then create the new query as a long chain of "OR (field["ID"] = "X")".from the results.

Either trigger will be much slower than if we could do this directly against the database, either within the IM Query functionality or by using raw SQL. The query generated by the trigger will also grow stale over time, depending on how much the results of the original queries change, so you will want to rerun it as often as is practical.

Hope that helps,

Matt