Extensis Logo SUPPORT | FORUMS | KNOWLEDGE BASE

Logical operator OR


#1

Please note our API 11.2 version doesn’t have the @operator enumerator “match_unparsed_query_string”.

I’m trying to run a really simple query to return paths that on queried on by their filename.

I seem to hit this error “A queryTerm may only have zero or two immediate subqueries.”. This is down to my lack of understanding and there isn’t any examples referring to using just the OR logical operator.

All we want to do is just either make a WHERE IN clause or build a bunch of OR statements. For example, “SELECT Path from Item_Table WHERE Filename =xxx OR Filename = xxx OR Filename = xxx”. We could potentially have any number of filenames to query on.

I will be greatful for any help on this! and if someone could even provide a simple example of how to achieve what I’ve described in the above paragraph.

Here is the C# code:-

[code]
assetQuery qry = new assetQuery();
List allTerms = new List();

//THIS IS USED TO RETURN ANY NUMBER OF FILENAME VALUES
string[] pathValues = PortfolioFilenameProvider.GetCommaDelimitedFilenames(oblique,false).Split(’,’);

//CREATE QUERYTERM FOR EACH VALUE
for (int i = 0; i < pathValues.Count(); i++)
{

           assetQueryTerm queryTerm = new assetQueryTerm();
           queryTerm.fieldName = "Path";
           queryTerm.@operator = queryOperator.equalValue;
           List<String> terms = new List<String>();
           terms.Add(pathValues[i]);
           queryTerm.values = terms.ToArray();
           allTerms.Add(queryTerm);

     
       }

       assetQueryTerm orQueryTerm = new assetQueryTerm();
       orQueryTerm.@operator = queryOperator.operatorOr;

//ADD ALL TERMS TO THE OR SUBQUERY - THIS IS WHERE I MIGHT BE MISUNDERSTANDING
orQueryTerm.subqueries = allTerms.ToArray();

       qry.queryTerm = orQueryTerm;

        assetQueryResultOptions resOptions = new assetQueryResultOptions();

        string catalogId = GetTargetCatalogId(ConfigurationManager.AppSettings["XXXXX"]);

        return  _service.getAssets(_sessionId, catalogId, qry, resOptions);[/code]

#2

Hi,

You’ll need to create a tree of query terms with ORs combining two sub-terms, like so:

                   OR
                 /     \  
              TermA     OR
                      /     \
                  TermB      OR
                           /     \
                       TermC     TermD

If you’d like, we can post some example code that turns your value array into this tree form, just let us know!

Thanks,
-Loren


#3

Yes please. Could you create some example code to this effect as I want to make sure I’m understand this correctly. This will give me the basis for what we need.

As I need to iterate over a large number of OR statements as we could potentially query hundreds of records.


#4

After some more testing today…

I have noticed somewhere in the region of 200 to 500 of subqueries. The API falls over with a Java stack overflow error and also it seems possible there is a limitation of the number of results that are returned.

The API version is 11.2.

I’m guessing I’m hitting limitations of the API for bulk operations. Could you confirm the limitations please?

I think we are looking at using a direct database connection.


#5

Hi,

We strongly recommend against using a direct database connection, as your code will break when you upgrade to future versions of Portfolio. If you use the API, your code will continue to work between versions.

We recommend against using more than a handful of subqueries in a query (10 should work fine). If you are looking for matches to hundreds of filenames, I would suggest looping over all the filenames and making a separate query for each, then combining the results. I think this would be the easiest solution to write and maintain.

If there is some reason why this is untenable, please let us know, and we can help you craft a solution!

Thanks,
-Loren