Delete an entry from many-to-many relation

Questions and Answers
1

Strapi Version: 3.0.0-beta.19.5
Operating System: Windows
Database: mysql
Node Version: 10.20.0
NPM Version: 6.14.4

I have two collection types ‘Users’ & ‘Builds’. There exist many to many relation between User & Build say “builds_users__users_builds”. How do I delete an entry in the table “builds_users__users_builds”.

Here is a snippet I used to get required data using raw select statement.

const builds = await strapi.connections.default.raw("
      SELECT *
      FROM builds_users__users_builds
      WHERE user_id = ${user.id}
      AND build_id IN (
          SELECT id
          FROM builds
          WHERE folder IN (
              SELECT id
              FROM folders
              WHERE project = ${project.id}
          ")
      );

It’s equivalent code using model is (which doesn’t work)

const builds = await strapi.query("build").model.query((qb) => {
        qb.from("builds_users__users_builds");
        qb.where("user_id", user.id);
        qb.andWhere("build_id", "in", function() {
          this.select("id").from("builds")
            .whereIn("folder", function() {
             this.select("id")
                .from("folders")
                .where("project", project.id);
            });
        });
      })
      .fetchAll()
      .then((res) => res.toJSON());

It throws error saying ER_BAD_TABLE_ERROR.

Please inform how to delete an entry from a table formed using many-to-many relation.

2

I have encountered these kind of issues so many times now.
You should not query the model, it doesn’t work, I have no idea why.
What you should do instead, is querying the DB directly.

qb.from("builds_users__users_builds");: what you are doing here is querying a Bookshelf model using Knex syntax

What you could try to do is querying the DB using Knex directly:

const knex = strapi.connections.default;

Then something like

//you should check out knex docs
const res = knex('build')
     .join("builds_users__users_builds")
        .where("user_id", "user.id");
        .andWhere("build_id", "in", function(qb) {
          qb.select("id").from("builds")
            .whereIn("folder", function(qb) {
             qb.select("id")
                .from("folders")
                .where("project", "project.id");
            });
        });

I am honestly very disappointed by this ORM

Anyway, this WHERE project = ${project.id} is not the way it should be done. This is subjected to sql injection attacks.

It should be like this ( according to knex’s documentation):

WHERE project = ?" ), project.id)
3

Hey @mijorus, thanks for the explanation. Really helped me out. I had to delete some entries from the table formed using two collection types and you’re code worked.

Didn’t really knew writing raw queries like I’ve done earlier could lead to SQL injection attacks.
Thanks for the additional info too.

1 Like
4

@rithuacharya I am facing the same problem like you. The query you have asked is more on querying the record, but how do you delete the entry from many-to-many relation? Do you mind to share how do you solve this?

5

@tention Sorry for the delay.
Here’s how I did it

const items = await strapi.connections.default("t1_t2__t2_t1")
        .where("t2_id", "in", [array of id's])
        .andWhere("t1_id", "in", function(qb) {
          qb.select("id").from("t1")
            .whereIn("col-x", function(qb) {
              qb.select("id")
                .from("t3")
                .where("col-y", ID);
            });
        })
        .del();

t1, t2: are table names
col-x, col-y: column name in table t1 & t3 respectively
items: no of items deleted

You can use any conditions in there

1 Like
6

I managed to do figure it out awhile back but thanks for getting back. :slight_smile: