How to join the Document and ClassDefinition objects when using SearchSQL?

Question

I'm using FileNet P8 Content Engine 5.5.x with the Java API to construct an ad-hoc query using SearchSQL. Creating a select statement based off of the Document class is straight-forward:

SearchSQL sql = new SearchSQL();
String selectList = "d.This, d.Id, d.Name, d.ClassDescription";
sql.setSelectList(selectList);
sql.setFromClauseInitialValue("Document", "d", true);
SearchScope scope = new SearchScope(conn.getObjectStore());
RepositoryRowSet rows = scope.fetchRows(sql, 8, null, null);

Note: The statement above is using the ClassDescription object, which you 'could' use but it would require navigating the object to get to the SymbolicName. You would also have to be carefull and craft the property filter to not cause a round-trip back to the server.

However, adding a join to the ClassDefinition class has not been successful:

SearchSQL sql = new SearchSQL();
String selectList = "d.This, d.Id, d.Name, d.ClassDescription";
sql.setSelectList(selectList);
sql.setFromClauseInitialValue("Document", "d", true);
sql.setFromClauseAdditionalJoin(JoinOperator.INNER,"ClassDefinition","cd","d.This", JoinComparison.EQUAL,"cd.Id",false);
SearchScope scope = new SearchScope(conn.getObjectStore());
RepositoryRowSet rows = scope.fetchRows(sql, 8, null, null);

Note: This version suffers from the same problem as the first one.

The problem is what to use as the join variable ("d.This") from the Document class, everything I've tried has thrown some type of SQL Syntax Exception. The kicker is that if I had access to the Oracle database I believe the join is simple.

SELECT dv.object_id, dv.u2e_documenttitle, cd.symbolic_name 
FROM DocVersion dv 
INNER JOIN ClassDefinition cd ON
dv.object_class_id = cd.object_id

At the end of the day, what I'm try to achieve is to get the Symbolic Name of the ClassDefinition class in the returned result set.

Answer 1

Having read most of the online API Documentation and still not getting any closer to an answer, I opened a ticket with IBM Support. Here is the correct way to join the Document Class to the Class Definition Class via the ad-hock SQL Query API

        var sql = new SearchSQL();

        sql.setFromClauseInitialValue("Document", "d", true);
        sql.setFromClauseAdditionalJoin(JoinOperator.INNER, "ClassDefinition","cd","d.ClassDescription", JoinComparison.EQUAL,"cd.This",true);

        var scope = new SearchScope(os);
        var pf = new PropertyFilter();
        pf.addIncludeProperty(new FilterElement(1, null, null, PropertyNames.THIS, null));
        pf.addIncludeProperty(new FilterElement(1, null, null, PropertyNames.ID, null));
        pf.addIncludeProperty(new FilterElement(1, null, null, "DocumentTitle", null));
        pf.addIncludeProperty(new FilterElement(1, null, null, PropertyNames.MIME_TYPE, null));
        pf.addIncludeProperty(new FilterElement(1, null, null, PropertyNames.DATE_CREATED, null));
        pf.addIncludeProperty(new FilterElement(1, null, null, PropertyNames.SYMBOLIC_NAME, null));
        pf.addIncludeProperty(new FilterElement(1, null, Boolean.FALSE, PropertyNames.CLASS_DESCRIPTION, null));
        pf.addIncludeProperty(new FilterElement(1, null, Boolean.FALSE, PropertyNames.CLASS_DEFINITION, null));
        pf.addIncludeProperty(new FilterElement(1, null, Boolean.FALSE, PropertyNames.PROPERTY_DEFINITIONS, null));
        pf.addIncludeProperty(new FilterElement(1, null, null, PropertyNames.CONTENT_ELEMENTS_PRESENT, null));

        RepositoryRowSet rows = scope.fetchRows(sql, 256, pf, null);