Fetching data that belongs to a model 'through' another relationship

Questions and Answers Strapi Backend
1

Hi there,

I have the following schema:

Article
Category
Tag

Each Article belongs to a Category, and an Article can have many Tags. So something like this:

{
  id: 1,
  title: "My article",
  category: {
    id: 1,
    name: "News"
  },
  tags: [
    {
      id: 3,
      name: "Tag 3"
    },
    {
      id: 4,
      name: "Tag 4"
    }
  ]
}

When viewing the ‘News’ category, I’d like to be able to display all the tags associated with articles that belong to that category. So I’m looking to get something like this:

{
  id: 1,
  name: "News",
  articles: [
    {
      id: 1,
      title: "My article",
      tags: [...]
    },
   {
      id: 2,
      title: "Another article",
      tags: [...]
    }
  },
  tags: [ // Tags associated with all articles that belong to this category.
    {
      id: 3,
      name: "Tag 3"
    },
    {
      id: 4,
      name: "Tag 4"
    }
  ]
}

One way would be to fetch all articles, and extract the tags that way (but that seems somewhat inefficient).

Instead, I’ve written the following query that gets all the article Ids associated with the given category, and then uses that to find all the tags Ids in the join table:

// api/category/controllers/category.js
const getCategoryTags = async (category) => {
   let tagIds = await strapi.connections.default.raw(
      `SELECT * FROM articles_tags__tags_articles WHERE article_id in (${articleIds.join()})`
    );

    tagIds = JSON.stringify(tagIds.rows);
    tagIds = JSON.parse(tagIds).map((a) => a.tag_id);

    const tags = await strapi
      .query("tag")
      .model.query((qb) => {
        qb.where("id", "in", tagIds);
      })
      .fetchAll({
        withRelated: [
          {
            articles: (qb) => {
              qb.where("category_id", "=", category.id);
            },
          },
        ],
      });

    return tags.toJSON();
}

module.exports = {
  find: async (ctx) => {
    const { slug } = ctx.query;

    const categories = await strapi
      .query("category")
      .find(ctx.query, ["articles.tags", "quote.person"]);

    if (slug && categories[0]) {
      categories[0].tags = await getCategoryTags(categories[0]);
    }

    return categories;
  },
  findOne: async (ctx) => {
    const category = await strapi
      .query("category")
      .findOne({ id: ctx.params.id }, ["articles.tags"]);

    category.tags = await getCategoryTags(category);

    return category;
  },
};

The above works perfectly, but I’d like to find a way to translate this over to Graph QL. I’m unsure whether:

a.) GraphQL already has a built in way of doing the above (which would make the above code redundant)

b.) If not, what is the best way to implement this? Do I need to write a custom resolver?

Any help greatly appreciated!

1 Like
3

Hi @sebpowell, sorry I’m not posting to help but I actually have a question about your strapi raw query, above. What does the ${articleIds.join()} in your code stand for? How do you interpolate these variables directly into the SQL query? I’m facing a similar situation of wanting to use a WHERE clause for the currently logged in user, and wondering how you did it… could you share some of that code for ${articleIds.join()}?

4

Thats a standart array method. It joins the array ["1", "2", "3"] into a string 1,2,3

So the query became:
SELECT * FROM articles WHERE id in (1,2,3)

1 Like
5

Thanks Sunny! Actually I was looking for a way to use the User object in my strapi raw queries. Went on a hunt on Github after seeing this, and found that I can simply use the user object ${ctx.state.user} in the SQL statement itself!

strapi.connections.default.raw(`SELECT author FROM posts WHERE author = ${ctx.state.user.id}`);
1 Like
6

@sebpowell, did you ever resolve the original issue? I am looking to do the same and have not been able to find the correct approach.

2 Likes