Strapi filter by many to many relation

Question

I have two tables in postgresql in strapi: goods and labels

Goods:
id | title
---------
1 | some name
2 | some name 2
3 | some name 4
…………………

Labels
id | title
------------
1 | some label 1
2 | some label 2
3 | some label n
………………………

They are connected with each other many to many, through an additional table

goods_labels__labels_goods
id | good_id      | label_id
----------------------------
1  |  1           | 1
2  |  1           | 2
2  |  2           | 1

I need to select goods that have a relationship with both label.id = 1 and label.id = 2, this is only the first product with id = 1. Is any way to implement it with standart strapi tools with graphql or with bookshelf, or as a last resort through a raw query? Also I need to sort and limit this request

Answer 1

ok. the content is here.

I have two content-type in strapi like tag and article. And want to filter articles which both have several tags like 1,2,4. In the standard strapi, you cannot do this .so I have to change the default behavior of strapi article api. you can find the document in here

first

we need to define the special parameter to reach our goals. in fact I choose the multiTag in the query parameter

query{
    multiTag: [1,2,3]   
}

second

open file in your project

/src/api/article/controllers/article.js

! notice: the path would change with your content-type name.

you should get this

const { createCoreController } = require('@strapi/strapi').factories;

module.exports = createCoreController('api::article.article');

then add some code to this

async function getMultiTag(conn, multiTag){
    // the sql should like this 
    const sql = `
        select 
            t.id 
        from 
            article t 
        inner join (
            select article_id from article_link_tag 
            where tag_id in (${string(multiTag})
            group by article_id having count(*) = ${multiTag.length}
        )t2 on t.id = t2.article_id
    `
    const result = await conn.raw(sql)
    // result should like this 

[
    // actual data
    [
        {"id" : 1}  
    ],
  // meta info
    [
    {
        "db" : 1
    }
    ]
]

    return result[0]
}

module.exports = createCoreController('api::pet-article.pet-article', ({ strapi }) => ({
   async find(ctx) {
    ctx.query = { ...ctx.query, local: 'en' }
        // get the param
    const multiTag = ctx.query?.multiTag
    if (multiTag && typeof multiTag == 'object' && multiTag.length > 0) {
            // get the database connection
      const conn = strapi.db.connection
      const articles = await getMultiTag(conn, multiTag)
            // when you have got the ids of article 
            // you can do many things
            // like this 
            ctx.query.filter = {
                ...(ctx.query.filter || {}),
                id: {
                    $in: articles.map(val => val.id)
                }
            }
            // or just return the ids
            // return { data: articles }
    }
    const { data, meta } = await super.find(ctx);
    meta.date = Date.now()
    return { data, meta };
  },
}));