DEV Community

Cover image for STRAPI - Fetching data belongs to a model through another relationship (Graph database)
Aastha Talwaria
Aastha Talwaria

Posted on

6 1

STRAPI - Fetching data belongs to a model through another relationship (Graph database)

Suppose we have the following in schema:

  • country
  • homepage
  • topics
  • sources
  • sections
  • articles

And this is how they are related

ER diagram

  • homepage has one country.
  • homepage has many topics.
  • homepage has many sources.
  • topics belongs to many sections.
  • sources belongs to many sections.
  • sections belongs to many articles.

Requirement:
Get top 10 the articles for homepage having country id as countryId sorted by attribute_name.

My Approach:

async function findTopArticleByCountryId({ countryId, sortBy = 'likeCount' }) {
  try {
    const response = await strapi.connections.default.raw(
      `SELECT * FROM articles WHERE section IN (
        SELECT id FROM sections WHERE 
        topic IN (
          SELECT id FROM topics WHERE homepage IN (
            SELECT id FROM homepage WHERE country = ${countryId}
          )
        )
        OR service IN (
          SELECT id FROM sources WHERE homepage IN (
            SELECT id FROM homepage WHERE country = ${countryId}
          )
        )
      ) ORDER BY ${sortBy} DESC LIMIT 10;`
    );
    const [articles] = response;
//sanitize articles entity
    return articles;
  } catch (e) {
    console.log(e);
    return false;
  }
}
Enter fullscreen mode Exit fullscreen mode

Let's discuss your approach in the discussion box or you can hit me up at aastha.talwaria29@gmail.com.

Thanks for reading.

SurveyJS custom survey software

Simplify data collection in your JS app with a fully integrated form management platform. Includes support for custom question types, skip logic, integrated CCS editor, PDF export, real-time analytics & more. Integrates with any backend system, giving you full control over your data and no user limits.

Learn more

Top comments (0)

SurveyJS custom survey software

JavaScript Form Builder UI Component

Generate dynamic JSON-driven forms directly in your JavaScript app (Angular, React, Vue.js, jQuery) with a fully customizable drag-and-drop form builder. Easily integrate with any backend system and retain full ownership over your data, with no user or form submission limits.

Learn more