DEV Community

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

Posted on

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.

Discussion (0)