Question:

I'm using Supabase with TypeScript and I'm having trouble typing the response when performing JOIN operations between tables. Before implementing the JOIN operation, I was using the type like this:

我正在使用带有TypeScrip的Supabase，在执行表之间的连接操作时，我在键入响应时遇到了问题。在实现联接操作之前，我使用的类型如下：

try {
  data = await fetchIngresosForMonth(firstDayOfMonth, lastDayOfMonth) as Tables<"ingresos">[];
} catch (error) {
  console.error("Error fetching data:", error);
  // TODO: Handle the error appropriately, maybe render an error message
}

After adding the JOIN operation, I extended the type and it seems to be working. However, I'm interested in knowing if this is the correct or most efficient approach. Below is how I extended the type:

在添加连接操作之后，我扩展了类型，它似乎正在工作。然而，我有兴趣知道这是正确的还是最有效的方法。下面是我是如何扩展该类型的：

Here's my current code:

以下是我当前的代码：

//components/tablaIngresos.tsx
import { createServerComponentClient } from "@supabase/auth-helpers-nextjs";
import { cookies } from "next/headers";
import { Database, Tables } from "@/types/supabase";
import { DbResult, DbResultErr, DbResultOk } from "@/types/dBTypes";

async function fetchIngresosForMonth(
  firstDayOfMonth: string,
  lastDayOfMonth: string,
): Promise<DbResultOk<any>> {
  const supabase = createServerComponentClient<Database>({ cookies });
  const query = supabase
    .from("ingresos")
    .select(`
    id,
    fuente,
    cantidad,
    timestamp_with_time_zone,
    user_id,
    FuenteIngreso:fuente (id, fuente_name)
  `)
    .gte("timestamp_with_time_zone", firstDayOfMonth)
    .lte("timestamp_with_time_zone", lastDayOfMonth);

  const result: DbResult<typeof query> = await query;
  // ... rest of the code
}

I tried extending the type like this:

我尝试像这样扩展类型：

type ExtendedIngresosRow = Tables<"ingresos"> & { FuenteIngreso: { fuente_name: string } };

I also tried another approach:

我还尝试了另一种方法：

type ExtendedIngresosRow = Tables<"ingresos"> & { FuenteIngreso: Pick<Tables<"FuenteIngreso">['Row'], 'fuente_name'> };

              <tbody className="text-black dark:text-white dark:bg-gray-900">
                {data?.slice(0, maxRows).map((item, index) => (
                  <tr key={index} className="border-b dark:border-neutral-500">
                    <td className="whitespace-nowrap px-6 py-4 font-medium">
                      {index + 1}
                    </td>
                    <td className="whitespace-nowrap px-6 py-4">
                      {item.FuenteIngreso.fuente_name}
                    </td>
                    <td className="whitespace-nowrap px-6 py-4">
                      {item.cantidad}
                    </td>
                    <td className="whitespace-nowrap px-6 py-4">
                      {item.timestamp_with_time_zone}
                    </td>
                  </tr>
                ))}
              </tbody>

Is my approach correct? Or does Supabase offer a better way to handle this?

我的做法正确吗？或者，Supabase提供了处理这一问题的更好方法吗？

The problem with your approach is that now your application is thinking of entities as database structures, which are always tables for Relational databases like PostgreSQL, which is what Supabase offers right now. These tables do not reflect real life nor how your application understands your business. What I would suggest you do is have separate types for your business entities that are independent of your tables and then use a mapper between your supabase client and the rest of the app, so nowhere in your app you would use "Tables" and "Rows": as soon as you get that from the API you map them to real business objects, and right before calling the API you map objects to database tables if needed.

您的方法的问题是，现在您的应用程序将实体视为数据库结构，它们始终是PostgreSQL等关系型数据库的表，这正是Supabase目前提供的。这些表既不反映现实生活，也不反映您的应用程序如何理解您的业务。我建议您为您的业务实体使用独立于表的单独类型，然后在您的超级数据库客户端和应用程序的其余部分之间使用映射器，因此在应用程序中的任何地方都不会使用“表”和“行”：一旦从API获得它们，就将它们映射到真正的业务对象，如果需要，就在调用API之前将对象映射到数据库表。

Now, this doesn't mean you need to have duplicate types (or maybe it does, depends on your choice). You can try to derive your business types from your Database types, eg:

现在，这并不意味着您需要重复的类型(或者可能需要，取决于您的选择)。您可以尝试从数据库类型派生您的业务类型，例如：

type Ingreso = Tables<"ingresos">['Row'] & { FuenteIngreso: Pick<Tables<"FuenteIngreso">['Row'], 'fuente_name'> };

(haven't tried my code, I am hadcrafting so it may fail)

(我还没有试过我的代码，我正在制作，所以它可能会失败)

The advantage of this approach is that you have less duplication and if a type changes on the database it automatically reflects in your code... this is only theory as your logic should change as well and also, how often do we change data types of a column?
A disadvantage is that your entities properties now match your DB styles (you now have to use snake_case in your business object types), and also they may have properties you don't care for like date_created.

这种方法的优点是减少了重复，如果数据库上的类型发生更改，它会自动反映在代码中……这只是理论上的，因为您的逻辑也应该更改，而且，我们多久更改一次列的数据类型？一个缺点是，您的实体属性现在与您的DB样式匹配(您现在必须在业务对象类型中使用SOVINE_CASE)，而且它们可能具有您不关心的属性，如DATE_CREATED。

Alternatively you can create your own types with, yes, a lot of duplication in your types, but also more control and less pollution on your business objects. You do

或者，您可以创建自己的类型，是的，在您的类型中有大量的重复，但也可以对业务对象进行更多的控制和更少的污染。你有

type Ingreso = {
    id: number,
    cantidad: number,
    user: User,
    fuente: string, // if you just need the name
    fuenteIngreso: FuenteIngreso, // if you want the whole object for calculation needs
}