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)

#strapi #database #javascript #discuss

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.

Top comments (0)