Source: Router/maintenance/queries.js

import { query } from '@commtool/sql-query';

/**
 * Resolve objects of a specific type for an organization through owner group links.
 * Traversal: object(memberA)->group(member)->organization.
 *
 * @param {string} type
 * @param {any} rootUID
 * @returns {Promise<Array<{UID:any,Title:any}>>}
 */
export const getObjectsByGroupOwner = async (type, rootUID) => query(`
    SELECT ObjectBase.UID, ObjectBase.Title
    FROM ObjectBase
    INNER JOIN Links AS ownerLink ON (ownerLink.UID = ObjectBase.UID AND ownerLink.Type = 'memberA')
    INNER JOIN ObjectBase AS ownerGroup ON (ownerGroup.UID = ownerLink.UIDTarget AND ownerGroup.Type = 'group')
    INNER JOIN Links AS orgLink ON (orgLink.UID = ownerGroup.UID AND orgLink.Type = 'member')
    WHERE ObjectBase.Type = ? AND orgLink.UIDTarget = ?
    GROUP BY ObjectBase.UID
`, [type, rootUID]);