Repository rows search

For use cases that involve returning properties from multiple joined classes, you can use a repositoryRows() search. With this search, the caller has control over and specifies the entire SQL statement and can be any SQL supported by the CE query syntax.

The return information involves a collection of RepositoryRow types. The only field in this type is properties. Unlike the properties field returned in other types or interfaces such as Folder and Document, the properties field of RepositoryRow does not have an includes argument. The properties returned are exactly what is specified in the SQL statement.

Through the SQL statement, it is possible for the caller to specify an alias for certain selected properties. This may be desirable, for example, when selecting the same property on multiple classes. There is an alias field of the CommonProperty interface that indicates whatever alias is specified by the caller. It is the same as the property symbolic name by default.

Handling object-valued properties in joined class searches

It is still possible to further shape object-valued properties that are selected in the SQL just like shaping complex fields or object-valued properties in the single object type searches. The following example selects Lender, an object valued property, as one of the row properties even though properties of the Lender class are being selected directly as one of the joined classes. This is for illustration purposes only; it is preferable to select the necessary properties from the joined class.

{
 repositoryRows(
   repositoryIdentifier:"OS1"
   sql:"SELECT a.Id, a.ApplicantName, a.DateLastModified, a.Lender, a.LoanType, a.LoanAmount, d.Id AS LenderId, d.LenderName, d.DateLastModified FROM LoanApplication a INNER JOIN Lender d ON a.Lender = d.This WHERE d.LenderName LIKE 'Local%' OPTIONS (COUNT_LIMIT 40)"
   pageSize:5
 )
 {
   repositoryRows {
     properties {
       id
       alias
       label
       type
       cardinality
       value
       ... on ObjectProperty {
         objectValue {
           className
		       ... on CmAbstractPersistable {
			       creator
			       dateLastModified
			       properties(includes: ["LenderName", "PrimaryContact"]) {
				       id
				       label
				       type
				       cardinality
				       value
			       }
		       }
	       }
	     }
     }
   }
   pageInfo {
     totalCount
     token
   }
 }
}

Handling property searches in virtual tables

If you defined a virtual table for an object store, you can use GraphQL to perform searches involving virtual tables. For more information, see the topic Creating virtual tables.

The following example uses the GenAI::VectorSearch virtual table from the GenAI Extensions add‑on to perform a vector search. It uses the repositoryRows() query to search for the GenaiScore and GenaiVectorChunks virtual properties from the virtual table in addition to the properties from the underlying class.

{
  repositoryRows(
    repositoryIdentifier:"p8os1",
    sql: "SELECT [Creator], [DateCreated], [LastModifier], [DateLastModified], [Id], [Name], [GenaiScore], [GenaiVectorChunks] FROM GenAI::VectorSearch(Document,'Legal Policy') where Creator='p8admin' and VersionStatus=1"
  ) {
    repositoryRows {
      properties {
        alias
        type
        cardinality
        value
      }
    }
  }
}

Handling pagination in repositoryRows query

To retrieve additional pages of results started with repositoryRows(), use the moreRepositoryRows() query.

{
 moreRepositoryRows(
   token:”<insert_token>”
 )
 {
   repositoryRows {
     properties {
       id
       alias
       label
       type
       cardinality
       value
       ... on ObjectProperty {
         objectValue {
           className
		       ... on CmAbstractPersistable {
			       creator
			       dateLastModified
			       properties(includes: ["LenderName", "PrimaryContact"]) {
				       id
				       label
				       type
				       cardinality
				       value
			       }
		       }
	       }
	     }
     }
   }
   pageInfo {
     totalCount
     token
   }
 }
}