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

-- 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 use a "UNION ALL" to include proxies in the results. If you don't need the proxies you can add a "AND [ecm:isProxy](http://ecmisProxy) = 0" clause to reduce the size of the query.

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

Note that only prefetched properties 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).

There is LIMIT because Page Provider for navigation use paging by default. If you have more than 200 documents in a folder you will not see the total size of results. See https://jira.nuxeo.com/browse/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'