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 schemas configured as prefetched are loaded. If you need to access a property that is not in a prefetched schema 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:isTrashed = 0
-- 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'