backend/database/migrations/2022.11.16T00.00.00.remove_duplicated_relationships.js (view raw)
1"use strict";
2
3/**
4 * Get the link tables names that need to be updated
5 */
6const getLinkTables = ({ strapi }) => {
7 const contentTypes = strapi.db.metadata;
8 const tablesToUpdate = {};
9
10 contentTypes.forEach((contentType) => {
11 // Get attributes
12 const attributes = contentType.attributes;
13
14 // For each relation type, add the joinTable name to tablesToUpdate
15 Object.values(attributes).forEach((attribute) => {
16 if (attribute.type === "relation" && attribute.joinTable) {
17 tablesToUpdate[attribute.joinTable.name] = attribute.joinTable;
18 }
19 });
20 });
21
22 return Object.values(tablesToUpdate);
23};
24
25async function up(trx) {
26 const linkTablesToUpdate = getLinkTables({ strapi });
27
28 // Remove duplicates from link tables
29 for (const table of linkTablesToUpdate) {
30 const tableExists = await trx.schema.hasTable(table.name);
31 if (!tableExists) continue;
32
33 strapi.log.info(`Deleting duplicates of table ${table.name}...`);
34
35 try {
36 // Query to delete duplicates from a link table
37 let query = `
38 CREATE TEMPORARY TABLE tmp as SELECT DISTINCT t2.id as id
39 FROM ?? as t1 JOIN ?? as t2
40 ON t1.id < t2.id
41 `;
42 const pivotWhereParams = [];
43
44 // For each pivot column, add a on condition to the query
45 table.pivotColumns.forEach((column) => {
46 query += ` AND t1.?? = t2.??`;
47 pivotWhereParams.push(column, column);
48 });
49
50 // Create temporary table with the ids of the repeated rows
51 await trx.raw(query, [table.name, table.name, ...pivotWhereParams]);
52
53 // Delete repeated rows from the original table
54 await trx.raw(`DELETE FROM ?? WHERE id in (SELECT * FROM tmp)`, [
55 table.name,
56 ]);
57 } finally {
58 // Drop temporary table
59 await trx.raw(`DROP TABLE IF EXISTS tmp `);
60 }
61 }
62}
63
64async function down() {}
65
66module.exports = { up, down };