Examples of SQL Generated by VCS

Request all Documents as Administrator

SELECT * FROM Document

-- 1/ Get the result list (only ids)
SELECT _C1 FROM (
  SELECT hierarchy.id AS _C1
  FROM hierarchy
  WHERE ((hierarchy.primarytype IN ($1, ... $58)))
UNION ALL
  SELECT _H.id AS _C1 FROM hierarchy _H
  JOIN proxies ON _H.id = proxies.id
  JOIN hierarchy ON proxies.targetid = hierarchy.id
  WHERE ((hierarchy.primarytype IN ($59, ... $116)))) AS _T
LIMIT 201 OFFSET 0

-- 2/ load hierarchy fragment for the 12 documents
SELECT id, parentid, pos, name, isproperty, primarytype, ...
FROM hierarchy
WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)

-- 3/ load prefetch dublincore fragment
SELECT id, creator, source,nature, created, description, ...
FROM dublincore
WHERE id IN ($1, $2,$3, $4, $5, $6, $7, $8, $9, $10, $11, $12)

-- 4/ load dublincore multi valued contributors fields
SELECT id, item FROM dc_contributors
WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
ORDER BY id, pos

-- 5/ load other fragments dc_subject, misc
SELECT ...
-- 6/ load ACL
SELECT id, name, grant, permission, user,group
FROM acls
WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9,$10, $11, $12)
ORDER BY id, pos

The main request uses a UNION ALL to include proxies in the results. If you don't need the proxies you can add a "AND ecm:isProxy = 0" clause to reduce the size of the query.

Note that requests to load fragments (steps 2 to 6) are not needed (or will be reduced) if the needed rows are already in the cache.

Note that only properties configured as prefetched are loaded. If you need to access a property that is not prefetched for all your documents, you will have an extra database access for each documents (lazy loading, also called the N+1 problem).

There is a LIMIT in the queries because the Page Provider for navigation uses paging by default. If you have more than 200 documents in a folder you will not see the total size of results. See NXP-9494 for more information.

List Children of a Folder Ordered by Title

SELECT * FROM Document
WHERE ecm:parentId = ? AND
      ecm:isVersion = 0 AND
      ecm:mixinType != 'HiddenInNavigation' AND
      ecm:currentLifeCycleState != 'deleted'
-- defaultSortColumn=dc:title

SELECT _C1, _C2 FROM (
  SELECT hierarchy.id AS _C1, _F1.title AS _C2
  FROM hierarchy
  LEFT JOIN dublincore _F1 ON hierarchy.id = _F1.id
  LEFT JOIN misc _F2 ON hierarchy.id = _F2.id
  JOIN hierarchy_read_acl _RACL ON hierarchy.id = _RACL.id
  WHERE ((hierarchy.primarytype IN ($1, ... , $33)) AND
        (hierarchy.parentid = $34) AND
        (hierarchy.isversion IS NULL) AND
        (_F2.lifecyclestate <> $35)) AND
        _RACL.acl_id IN (SELECT * FROM nx_get_read_acls_for($36))
UNION ALL
  -- same select for proxies
ORDER BY _C2
LIMIT 201 OFFSET 0

Select on a Complex Type

SELECT * FROM Document WHERE files/*/file/name LIKE '%.jpg'

SELECT DISTINCT _C1 FROM (
  SELECT hierarchy.id AS _C1
  FROM hierarchy
  LEFT JOIN hierarchy _H1 ON hierarchy.id = _H1.parentid AND _H1.name = $1
  LEFT JOIN hierarchy _H2 ON _H1.id = _H2.parentid AND _H2.name = $2
  LEFT JOIN content _F1 ON _H2.id = _F1.id
  WHERE  ((hierarchy.primarytype IN ($3, ... $60)) AND
   (_F1.name LIKE $61))
  UNION ALL
    -- same select for proxies AS _T
 LIMIT 201 OFFSET 0
-- parameters: $1 = 'files', $2 = 'file' .. $61 = '%.jpg'