[V4] QueryEngine - deleteMany not working when filter by nested entity.

[c7ming]

Bug report

Describe the bug

When trying to deleteMany by the nested entity filtering, An error occurs.

Steps to reproduce the behavior

1. Have eneties A & B.
2. Add relation between A & B, and B has many A.
3. Execute codes

Expected behavior

All B filtered A will be deleted.

Screenshots

Code snippets

await strapi.query(A).deleteMany({ where: { B: { id: { $eq: params.id }, } } })

System

• Node.js version: V14
• NPM version: 7.22.0
• Strapi version: 4.0.1
• Database: MySql
• Operating system: MacOs Monterey

[iicdii]

I reproduced it with sqlite too

[Error: delete from `addresses` where (`t2`.`id` = 1) - SQLITE_ERROR: no such column: t2.id] {
  errno: 1,
  code: 'SQLITE_ERROR'
}

And I found some evidences that it's WIP

strapi/packages/core/content-manager/server/services/entity-manager.js

Lines 202 to 207 in 0f9b692

	// FIXME: handle relations
	deleteMany(opts, uid) {
	const params = { ...opts };
	return strapi.entityService.deleteMany(uid, params);
	},

strapi/packages/core/database/lib/entity-manager.js

Lines 324 to 340 in e5ee7f0

	// TODO: where do we handle relation processing for many queries ?
	async deleteMany(uid, params = {}) {
	await db.lifecycles.run('beforeDeleteMany', uid, { params });
	const { where } = params;
	const deletedRows = await this.createQueryBuilder(uid)
	.where(where)
	.delete()
	.execute();
	const result = { count: deletedRows };
	await db.lifecycles.run('afterDelete', uid, { params, result });
	return result;
	},

[iicdii]

Temporary workaround

assume that..

api::address.address - A
api::category.category - B (has many addresses)

Example

let addressItems;
try {
  addressItems = await strapi
    .query("api::address.address")
    .findMany({ where: { category: { id: 1 } } });
} catch (e) {
  console.error(e);
}

try {
  for (const address of addressItems) {
    await strapi
      .query("api::address.address")
      .delete({ where: { id: address.id } });
  }
} catch (e) {
  console.error(e);
}

[rcdk]

I would also love to get this fixed :-)

[strapi-bot]

This issue has been mentioned on Strapi Community Forum. There might be relevant details there:

https://forum.strapi.io/t/find-and-filtering-entries-with-deletemany/21679/2

[antokhio]

Just came across this, with oneToOne relation, strapi version 4.3.2
Works:

let tasks = await strapi.db.query("api::task.task").findMany({
  where: {
    parent: section.id,
  },
});

Doesn't work:

let tasks = await strapi.db.query("api::task.task").deleteMany({
  where: {
    parent: section.id,
  },
});

Error message:

error: delete from "tasks" using "tasks_parent_links" as "t1","sections" as "t2" where ("t2"."id" = $1) and "t0"."id" = "t1"."task_id" and "t1"."section_id" = "t2"."id" - missing FROM-clause entry for table "t0"

[borisNG]

i'm currently having this issue do you have any clue on how to solve that?

[MHase]

I had exactly the same issue and I've managed to solve this by adding additional query

I wanted to remove all all products (lamps) which are related to common family
in my case I had to query for all related items and after that remove them one by one

I know it's not perfect, but for this deleteMany error should be enough 😞

still I hope that they will fix it in near future

beforeDelete: async (event) => {
  const { params } = event;

  const familyId = params.where.id;
 
  const lampsToDelete = await strapi.db.query("api::lamp.lamp").findMany({
    where: {
      family: {
        id: {
          $eq: familyId,
        },
      },
    },
  });

  await Promise.all(
    lampsToDelete.map(({ id }) =>
      strapi.db.query("api::lamp.lamp").delete({
        where: { id },
      })
    )
  );
},

[XanderD99]

Just encountered this issue as well.

Got it working with the code below. Seems like strapi just expects us to pass something in the id value in the where filter. So passing a query to match all ids in an array of numbers works just fine.

module.exports = {
  async beforeDelete({ params }) {
    const toDelete = await strapi.db.query('api::discord.channel').findMany({ where: { guild: params.where.id } });
    return strapi.db.query('api::discord.channel').deleteMany({ where: { id: { $in: toDelete.map(({ id }) => id) } } });
  },
}

[antokhio]

Just encountered this agian:
message collection type

{
  "kind": "collectionType",
  "attributes": {
    "chat": {
      "type": "relation",
      "relation": "oneToOne",
      "target": "api::chat.chat"
    },
  },
}

message has one chat

query:

await strapi.db.query("api::message.message").deleteMany({ where: { chat: chatId }});

result

[2022-11-14 21:07:03.662] error: delete from "messages" using "messages_chat_links" as "t1","chats" as "t2" where ("t2"."id" = $1) and "t0"."id" = "t1"."message_id" and "t1"."chat_id" = "t2"."id" - missing FROM-clause entry for table "t0"
error: delete from "messages" using "messages_chat_links" as "t1","chats" as "t2" where ("t2"."id" = $1) and "t0"."id" = "t1"."message_id" and "t1"."chat_id" = "t2"."id" - missing FROM-clause entry for table "t0"

temporary fix, use find, Promise.all

[itruf]

I have same issue with updateMany method:

await strapi.db.query("api::message.message").updateMany({
                        where: {
                            $and: [
                                { chat: decoded.chatId },
                                { in_context: true },
                            ],
                        },
                        data: {
                            in_context: false,
                        },
                    }).catch((e) => console.log("error", e.message));

[ItsHMS]

Just encountered this issue as well.

Got it working with the code below. Seems like strapi just expects us to pass something in the id value in the where filter. So passing a query to match all ids in an array of numbers works just fine.

module.exports = {
  async beforeDelete({ params }) {
    const toDelete = await strapi.db.query('api::discord.channel').findMany({ where: { guild: params.where.id } });
    return strapi.db.query('api::discord.channel').deleteMany({ where: { id: { $in: toDelete.map(({ id }) => id) } } });
  },
}

this one is perfectly working.Here is what i have done exactly this person did:
const itemsToDelete = await strapi.db .query("api::order-item.order-item") .findMany({ where: { order: { id: { $eq: entry?.id, }, }, }, }); console.log("itemsToDelete", itemsToDelete); const deleted = await strapi .query("api::order-item.order-item") .deleteMany({ where: { id: { $in: itemsToDelete.map((item) => item.id), }, }, });

[boazpoolman]

this one is perfectly working.Here is what i have done exactly this person did:
console.log("itemsToDelete", itemsToDelete); const deleted = await strapi .query("api::order-item.order-item") .deleteMany({ where: { id: { $in: itemsToDelete.map((item) => item.id), }, }, });

It's a nice workaround, but it's suboptimal in terms of performance.

[MaksZhukov]

Any updates from strapi?

[nguyenchauhuyen]

not yet

[notcacida]

Unfortunately I experienced the bug yesterday

[angelplusultra]

Experienced this bug today, used @XanderD99 solution and it works

[OdifYltsaeb]

I have the same issue with Postgresql, where I'm looking to update multiple addresses and filtering them by owner id ( FK to the user ). The issue has been open for close to 2 years already. Perhaps it's time to fix this?

[AlexanderFuse]

same issue, thanx @XanderD99, its working

[briankandersen]

As per today, this bug is still there. It's been almost 2 years now. What is going on? Why hasn't this been fixed yet?

[maximelafarie]

Error still there in 4.14.5.

Not to mention, I encountered this bug because I couldn't fix another existing bug in Strapi: the deletion of relations associated with a deleted parent data item, which is the basis of any ORM.

[giuliotiseo]

Same here, I adopt the solution by @XanderD99 for now but I think this is a serious issue that need to be fix as soon as possible. My case consists in a list of clinical records and some other stuff that need to be removed when the user delete the patient.

[AndriiKlymchuk]

Still occurs in the version 4.14.6
Since so many people experience it for a long time, @alexandrebodin could the team pay attention on it?

[Dimitris-Arabatzis]

This is an important defect. Please pay attention to this.

I created a helper function like this.

const deleteMany = async (tableName, whereClause) => { //Created because of known bug "deleteMany not working when filter by nested entity" Github: #11998 try { const toDelete = await strapi.db .query(tableName) .findMany({ where: whereClause }); return strapi.db .query(tableName) .deleteMany({ where: { id: { $in: toDelete.map(({ id }) => id) } } }); } catch (error) { console.error("Error deleting records from ${tableName}: ${error.message}"); throw error; } };

You can use it like this:

await deleteMany("api::teacher-degree.teacher-degree", { teacher: { id: teacherId });

[MaksZhukov]

Any updates from strapi team?

[alexandrebodin]

Hello all 👋

Sadly we cannot work on this issue at the moment because we are prioritizing other more critical issues. This one was defiend as low severity because a workaround exists for now. We are still a small team doing all our best to address the more urgent topics 🙏

If some of you are interested in contributing a fix let us know so we can guide you through the codebase 💯