Categories
Featured Posts Plumtree • BEA AquaLogic Interaction • Oracle WebCenter Interaction Software Development

Query filters explained, at last

I can’t even begin to count the number of times I’ve been asked how to write a query filter in Plumtree. Yesterday, I had to write one for myself and lo and behold, I was confused. I’ve come to the conclusion that they’re just hard to write. Despite this, they’re incredibly useful when trying to […]

I can’t even begin to count the number of times I’ve been asked how to write a query filter in Plumtree. Yesterday, I had to write one for myself and lo and behold, I was confused. I’ve come to the conclusion that they’re just hard to write. Despite this, they’re incredibly useful when trying to lookup objects in the Plumtree API.

(Note: this post does not apply to writing portlets or other integrations. It’s only useful when writing Pluggable Navigation, PEIs, or other customizations to the UI.)

To use a query filter, you’ll first need access to a Plumtree user’s session. Let’s assume that you have one in the variable plumtreeSession and that it’s already connected as a user who has at least read-level access to some objects. This query will work for any object, but just for the sake of example, let’s use Groups. Also, I wrote this in Java, but it works almost exactly the same way in C#.

First you need to get an IPTObjectManager as shown here:

IPTObjectManager groups = plumtreeSession.GetUserGroups();

Next, we’ll build up the query filter, which is always an Object[][].

Object[][] filter = new Object[3][1];
filter[0][0] = new Integer(PT_PROPIDS.PT_PROPID_NAME);
filter[1][0] = new Integer(PT_FILTEROPS.PT_FILTEROP_EQ_NOCASE);
filter[2][0] = "Everyone";

Let’s go through that line by line. First of all, why did I choose 3,1 for my array dimensions? All query filters are 2D arrays. The first dimension always has three items:

    1. The name of the property on which you’re filtering
    2. The operator (equals, less than, greater than, etc.)
    3. The value of the property you’re evaluating

Since it’s an Object[][], we can’t use primatives, so we must box our constants in Integer objects because the constants themselves are primatives.

When using the Query method that supports a query filter, you also need to specify an ordering attribute. You can only order ascending, but you can order by up to three properties which will be applied in the order they are put in the array, as shown below:

int[] orderBy = {PT_PROPIDS.PT_PROPID_NAME, PT_PROPIDS.PT_PROPID_OBJECTID};

In the code above, I’m asking to order first by name, then by object ID. (Since no two names can be the same in Plumtree, this dual ordering doesn’t really make much sense, but oh well.)

Now, finally, it’s time to run the query:

IPTQueryResult group = groups.Query(PT_PROPIDS.PT_PROPID_OBJECTID + PT_PROPIDS.PT_PROPID_NAME, -1, orderBy, 0, -1, filter);

Let’s break down those parameters one by one. The first, PT_PROPIDS.PT_PROPID_OBJECTID + PT_PROPIDS.PT_PROPID_NAME is a bitmask of properties you want to include in your results.

The second, -1, means query all folders in the admin directory. (If you want to restrict your query to a single folder (and all folders below it), use the folder id here. If you want to query down two distinct folder trees, you’ll need to run two queries.

The third, orderBy is your ordering attribute(s), explained above.

The fourth, 0 is your starting row.

The fifth, -1 is the number of rows to return (-1 means all rows). This parameter, along with the starting row, can be very useful for pagination.

The sixth and final parameter is your beloved query filter.

We did it! Now that wasn’t so bad, eh? Iterating through the results set is trivial, so I won’t cover it here, but if you have you have a question, feel free to make a comment here or post on the developer forums.

2 replies on “Query filters explained, at last”

Java doesn’t actually have multi-dimensional arrays: the filter is a ragged array. Although it sounds like I’m being pedantic, I think this fact explains why the Plumtree API uses a seemingly transposed array. Any programmer who’s _heard_ of databases feels that it would make more sense if filters indexed by row in the (pseudo) 2D table. However, that would make it easy to forget to fill in one of the three columns for every row, which wouldn’t be caught until runtime because the array is actually ragged.

So I have to commend the Plumtree developers for their novel usability solution, and scold the Java developers for forcing them to do it. 🙂

Interesting . . . I’ve always wondered why the array seemed to be “backwards” and that explains it – thanks!

If I had designed it, I would have made a QueryFilters object with a method addQueryFilter(Property operand, FilterOp operator, Object value) rather than making the developer deal with ragged arrays.

Leave a Reply to Jared Cancel reply