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.

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay