Q:

API: Queries for TestCases that have found the most defects

Is there an efficient way to query for which testcases have found the most defects?
I don't think you can find defects on test cases, and it doesn't look like you can query on testcases on defects (the field is there but not query-able).
So it seems if you want to find out which testcases have found the most defects, the only way to do it is to query all the defects and then count up the test cases on the client side.
Is there a better way?

A:

I too don't see a way to query for Defects on TestCases. There is a WorkProduct field, but that is intended to point to the Story (or Defect) that resulted in the TestCase being created rather than what you want. You want to inspect the TestCases field on Defects. Unfortunately, the TestCases field on Defects is not usable in a query expression in our standard WSAPI. However, you can include it in a query in our Lookback API.
This query will return all Defects that mention one or more TestCases:
{
    "find":{
        "_TypeHierarchy":"Defect",
        "TestCases":{"$exists":true},
        "__At":"current"
    },
    "fields":["ObjectID","TestCases"]
}

That will give you results like this:
[
    {
        "ObjectID": 1412057460,
        "TestCases": [
            1431813466
        ]
    },
    {
        "ObjectID": 1445019668,
        "TestCases": [
            1445020483
        ]
    },
    {
        "ObjectID": 2021743865,
        "TestCases": [
            2844922903,
            2844882838
        ]
    },
    {
        "ObjectID": 2047435117,
        "TestCases": [
            2082930376
        ]
    },
    {
        "ObjectID": 2458916959,
        "TestCases": [
            2082930376
        ]
    }
]

But that is still not what you want. You still need to find the max and the list of TestCases that have that max. This code does that. The key lines from that code are:
counts = {}
for r in results
  for t in r.TestCases
    unless counts[t]?
      counts[t] = 0
    counts[t]++

max = Math.max.apply({}, (value for key, value of counts))
testCasesWithMaxDefects = (key for key, value of counts when value is max)

alert("Test Case(s) with the maximum number of defects (#{max}) is(are) #{testCasesWithMaxDefects}")

Follow the link to see the JavaScript equivalent.

