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 };