使用SearchSQL时如何连接Document和ClassDefinition对象

Question

我使用FileNet P8 Content Engine 5.5.x和Java API来构造一个使用SearchSQL的特别查询。基于Document类创建一个select语句是直接的：

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);

注意：上面的语句使用的是ClassDescription对象，您"可以"使用该对象，但它需要导航该对象才能到达SymbolicName。您还必须小心地设计属性过滤器，以免导致往返服务器。

然而，向ClassDefinition类添加联接并没有成功：

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);

注意：此版本与第一个版本存在相同的问题。

问题是使用什么作为Document类的联接变量("d.This"(，我尝试过的所有操作都引发了某种类型的SQL语法异常。关键是，如果我可以访问Oracle数据库，我相信连接很简单。

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

最后，我试图实现的是在返回的结果集中获得ClassDefinition类的符号名称。

Answer 1

在阅读了大部分在线API文档后，仍然没有找到答案，我向IBM支持部门开了一张罚单。以下是通过ad-hock SQL查询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);