Svelte+Supabase：外键字段lawfirmname未与数据同行插入问题求助

问题根源 #

原代码处理每一行CSV数据时，每个字段都会单独生成一条记录插入子表——比如products表中，websitedevelopment是一条独立记录，lawfirmname又是另一条独立记录，导致外键无法和产品数据关联，最终出现数据分离的异常。

核心修复思路 #

遍历每一行数据时，先为每个目标表创建一个空对象，将该行中属于该表的所有字段（包括lawfirmname）合并到同一个对象中，最后再将完整的记录加入对应表的数据数组，确保一行CSV数据对应子表的一条完整关联记录。

修复后的完整代码 #

<script>
  import Papa from "papaparse";
  import { supabase } from "../../../lib/supabaseClient";

  const tableColumns = {
    lawfirm: [
      "lawfirmname",
      "clientstatus",
      "websiteurl",
      "address1",
      "address2",
      "city",
      "stateregion",
      "postalcode",
      "country",
      "phonenumber",
      "emailaddress",
      "description",
      "numberofemployees",
    ],
    lawyerscontactprofiles: [
      "firstname",
      "lastname",
      "email",
      "phone",
      "profilepicture",
      "position",
      "accountemail",
      "accountphone",
      "addressline1",
      "suburb",
      "postcode",
      "state",
      "country",
      "website",
      "lawfirmname",
    ],
    products: [
      "websitedevelopment",
      "websitehosting",
      "websitemanagement",
      "newsletters",
      "searchengineoptimisation",
      "socialmediamanagement",
      "websiteperformance",
      "advertising",
      "lawfirmname",
    ],
    websites: ["url", "dnsinfo", "theme", "email", "lawfirmname"],
  };

  let file,
    headers = [],
    data = [],
    columnMappings = [];

  $: headers = [...headers];
  $: data = [...data];
  $: columnMappings = [...columnMappings];

  async function handleFileChange(event) {
    try {
      file = event.target.files[0];
      if (!file) throw new Error("No file selected.");
      console.log("File selected:", file);
    } catch (err) {
      console.error("File selection error:", err.message);
      alert(err.message);
    }
  }

  async function handleFileUpload() {
    if (!file) {
      alert("Please select a file to upload.");
      return;
    }

    const reader = new FileReader();
    reader.onload = (event) => {
      const csvData = event.target.result;
      Papa.parse(csvData, {
        header: true,
        skipEmptyLines: true,
        complete: (results) => {
          headers = results.meta.fields;
          data = results.data;
          columnMappings = headers.map((header) => ({
            header,
            table: "",
            column: "",
          }));
        },
        error: (err) => console.error("Error parsing CSV:", err),
      });
    };

    reader.readAsText(file);
  }

  async function handleDataInsert() {
    const tables = {
      lawfirm: [],
      lawyerscontactprofiles: [],
      products: [],
      websites: [],
    };

    data.forEach((row) => {
      // 为当前行的每个表初始化一个空记录对象
      const rowRecords = {
        lawfirm: {},
        lawyerscontactprofiles: {},
        products: {},
        websites: {},
      };
      const lawfirmname = row["lawfirmname"]?.trim() || "";

      columnMappings.forEach(({ header, table, column }) => {
        if (table && column) {
          const value = row[header]?.trim() || "";
          // 如果是lawfirm表的字段，直接赋值到对应记录
          if (table === "lawfirm") {
            rowRecords[table][column] = value;
          } else {
            // 子表字段赋值，同时确保lawfirmname被加入
            rowRecords[table][column] = value;
            // 手动添加lawfirmname到子表记录（如果CSV行中有该值）
            if (lawfirmname) {
              rowRecords[table]["lawfirmname"] = lawfirmname;
            }
          }
        }
      });

      // 将当前行的完整记录加入到对应表的数组中（过滤空对象）
      Object.entries(rowRecords).forEach(([table, record]) => {
        if (Object.keys(record).length > 0) {
          // 处理lawfirm表的去重逻辑提前到这里
          if (table === "lawfirm") {
            const existing = tables.lawfirm.find(f => f.lawfirmname === record.lawfirmname);
            if (!existing) {
              tables.lawfirm.push(record);
            }
          } else {
            tables[table].push(record);
          }
        }
      });
    });

    try {
      for (const table in tables) {
        if (tables[table].length > 0) {
          const { error } = await supabase.from(table).upsert(tables[table], {
            onConflict: table === "lawfirm" ? ["lawfirmname"] : undefined,
          });
          if (error) {
            console.error(`Error inserting into ${table}:`, error.message);
          } else {
            console.log(`Successfully inserted into ${table}`);
          }
        }
      }
    } catch (error) {
      console.error("Error inserting data:", error.message);
    }
  }
</script>

<div class="homeBanner">
  <h1 class="leftAlign">Upload CSV Final Test</h1>
  <div class="searchAndAdd">
    <input type="file" accept=".csv" on:change={handleFileChange} />
    <button on:click={handleFileUpload}>Import CSV</button>
  </div>
</div>

{#if headers.length}
  <div class="mappingSection">
    {#each columnMappings as mapping, index}
      <div class="mappingRow">
        <label for="table-{index}">{mapping.header}</label>
        <select id="table-{index}" bind:value={mapping.table}>
          <option value="">Select table</option>
          {#each Object.keys(tableColumns) as table}
            <option value={table}>{table}</option>
          {/each}
        </select>
        {#if mapping.table && tableColumns[mapping.table]}
          <select id="column-{index}" bind:value={mapping.column}>
            <option value="">Select column</option>
            {#each tableColumns[mapping.table] as column}
              <option value={column}>{column}</option>
            {/each}
          </select>
        {/if}
      </div>
    {/each}
    <button class="insertButton" on:click={handleDataInsert}>Insert Data</button>
  </div>
{/if}

关键修改点说明 #

1. 每行记录单独收集：新增rowRecords对象，为当前CSV行的每个目标表存储一条完整记录，避免每个字段生成独立对象。
2. 子表自动关联外键：在处理子表字段时，自动将当前行的lawfirmname添加到子表记录中，确保外键与其他字段同属一条记录。
3. 提前去重lawfirm：在添加lawfirm记录时直接检查是否已存在，避免后续单独遍历去重的冗余操作。

内容的提问来源于stack exchange，提问作者user19102549