STRAPI V5 : Returns the full hierarchical chain of entries for the specified relation field (manyToOne)

getRelationChain.ts

//given an entity ID (not documentId),
//return the full chain of items for a relation field (manyToOne).
//NB: getRelationChainIds() uses a single DB request to retrieve the chain of IDs.

//How to use ?
//const chainItems = await getRelationChain(strapi,itemId,'api::category.category','parent');
//will return the whole hierarchy of items (as a flat ordered array) from the 'parent' field, for categories.

import _ from 'lodash';

/**
 * Returns the full hierarchical chain of entries for the specified relation field (manyToOne)
 * This function will return an array of document ids.
 *
 * @param {Object} strapi - The Strapi instance used to interact with the database.
 * @param {number} itemId - The ID (not documentId!) of the target entry.
 * @param {string} itemUid - The UID of the target entry (eg. 'api::category.category')
 * @param {string} attributeName - The name of the attribute (field) that stores the relation, e.g., 'parent'.
 * @param {Object} relationsQuery - Optionnal query parameter for the entries returned.  Empty by default (returns only id and documentId)
 * @returns {Promise<string[]>} - A promise that resolves to an array of entry IDs representing the full relation chain.
 */
 
export async function getRelationChain(strapi,itemId:number,itemUid:string,attributeName:string,relationsQuery?:any){

    const uidSchema = strapi.contentTypes[itemUid];
    if (!uidSchema){
      throw new Error('invalid UID');
    }
    const table_name = uidSchema.info.pluralName;//items table (for this itemId)
    const chainIds = await getRelationChainIds(strapi,itemId,itemUid,attributeName);
    if (!chainIds) return;


    //if we have an advanced query, use strapi.documents
    if (relationsQuery){
      const forceParams = {
        filters: {
          id: {
            $in: chainIds
          }
        }
      };
      const params = _.merge({}, relationsQuery, forceParams);
      return await strapi.documents(itemUid).findMany(params);
    }else{//just return the id and document id with a custom query, it's faster.
      const query = `
        SELECT id,document_id
        FROM ${table_name}
        WHERE id IN (${chainIds.map(() => '?').join(', ')})
      `;

      const result = await strapi.db.connection.raw(query, chainIds);

      // Format it as we would get a result from the service
      // TOUFIX TOUCHECK maybe there's a native Strapi method for this ?
      return result.map(row => ({
        id: row.id,
        documentId: row.document_id
      }));

    }

  }
/**
* Returns the full hierarchical chain of entries for the specified relation field (manyToOne)
* This function will return an array of ids (not document ids).
*
* @param {Object} strapi - The Strapi instance used to interact with the database.
* @param {number} itemId - The ID (not documentId!) of the target entry.
* @param {string} itemUid - The UID of the target entry (eg. 'api::category.category')
* @param {string} attributeName - The name of the attribute (field) that stores the relation, e.g., 'parent'.
*
* @returns {Promise<number[]>} - A promise that resolves to an array of entry IDs representing the full relation chain.
*/
async function getRelationChainIds(strapi,itemId:number,itemUid:string,attributeName:string){

  const uidSchema = strapi.contentTypes[itemUid];
  if (!uidSchema){
    throw new Error('invalid UID');
  }

  const attribute = uidSchema.attributes[attributeName];
  if (!attribute){
    throw new Error('invalid attribute');
  }

  if (attribute.type !== 'relation'){
    throw new Error("Attribute type should be 'relation'");
  }

  if (attribute.relation !== 'manyToOne'){
    throw new Error("Attribute relation should be 'manyToOne'");
  }

  const uidRelationSchema = strapi.contentTypes[attribute.target];
  if (!uidRelationSchema){
    throw new Error('invalid relation UID');
  }

  const pluralName = uidSchema.info.pluralName;
  const singularName = uidSchema.info.singularName;

  const table_name = `${pluralName}_${attributeName}_lnk`;//table that stores the relations for the field
  const id_col = `${singularName}_id`;//column for item ID
  const inv_id_col = `inv_${singularName}_id`;//column for parent ID

  const queryStr = `
    WITH RECURSIVE ParentHierarchy AS (
      SELECT ${id_col}, ${inv_id_col}
      FROM ${table_name}
      WHERE ${id_col} = ?

      UNION ALL

      SELECT l.${id_col}, l.${inv_id_col}
      FROM areas_parent_lnk l
      INNER JOIN ParentHierarchy ph ON l.${id_col} = ph.${inv_id_col}
    )
    SELECT ${id_col}, ${inv_id_col} FROM ParentHierarchy;
  `;
  const result = await strapi.db.connection.raw(queryStr, [itemId]);

  const chainIds = result
  .map(row => row[inv_id_col])
  .filter(Boolean);

  return chainIds;
};