typescript - How to get a row from a table as well as the one to many relations from another seperate table using Drizzle ORM? -

admin管理员组

文章数量:1122832

I have this Drizzle ORM schema for one of my Cybersecurity projects:

export const ips = createTable("ips", {
  id: uuid("id")
    .primaryKey()
    .default(sql`gen_random_uuid()`),
  ipAddress: varchar("ip_address").notNull().unique(),
  city: varchar("city", { length: 64 }).notNull(),
  region: varchar("region", { length: 64 }).notNull(),
  country: varchar("country", { length: 64 }).notNull(),
  latitude: varchar("latitude", { length: 64 }).notNull(),
  longitude: varchar("longitude", { length: 64 }).notNull(),
  timezone: varchar("timezone", { length: 64 }).notNull(),
  asn: varchar("asn", { length: 64 }).notNull(),
  isp: varchar("isp", { length: 64 }).notNull(),
  updatedAt: timestamp("updated_at").defaultNow(),
  createdAt: timestamp("created_at").defaultNow(),
});

export const ports = createTable(
  "ports",
  {
    ipId: uuid("ip_id").references(() => ips.id, { onDelete: "cascade" }),
    port: integer("port").notNull(),
    service: varchar("service", { length: 64 }).notNull(),
    rawData: text("raw_data"),
    updatedAt: timestamp("updated_at").defaultNow(),
    createdAt: timestamp("created_at").defaultNow(),
  },
  (table) => {
    return {
      pk: primaryKey({ columns: [table.ipId, table.port] }),
    };
  },
);

export const portsRelations = relations(ports, ({ one }) => ({
  ports: one(ips, {
    fields: [ports.ipId],
    references: [ips.id],
  }),
}));

I am trying to write a search function using Drizzle ORM. Here is what I have so far:

export const panginatedSearchResultsSchema = z.object({
  page: z.coerce.number().default(1),
  per_page: z.coerce.number().default(10),
  sort: z.string().optional(),
  query: z.string().optional(),
  ipAddress: z.string().optional(),
  port: z.coerce.number().optional(),
  service: z.string().optional(),
});

export type GetPaginatedSearchResult = z.infer<
  typeof panginatedSearchResultsSchema
>;

export async function getPaginatedSearchQuery(input: GetPaginatedSearchResult) {
  noStore();
  const conditions = [];

  if (input.ipAddress)
    conditions.push(ilike(ips.ipAddress, `%${input.ipAddress}%`));

  if (input.port) conditions.push(eq(ports.port, input.port));

  if (input.service)
    conditions.push(ilike(ports.service, `%${input.service}%`));

  if (input.query) {
    const searchText = `%${input.query}%`;
    conditions.push(
      or(
        ilike(ips.ipAddress, searchText),
        ilike(ports.service, searchText),
        ilike(ports.rawData, searchText),
      ),
    );
  }

  const offset = ((input.page || 1) - 1) * (input.per_page || 10);
  const limit = input.per_page || 10;

  const [column, order] = (input.sort?.split(".") as [
    keyof typeof ips.$inferSelect | undefined,
    "asc" | "desc" | undefined,
  ]) ?? ["created_at", "desc"];

  const orderBy =
    column && column in ips
      ? order === "asc"
        ? asc(ips[column])
        : desc(ips[column])
      : desc(ips.createdAt);

  const dataQuery = db
    .select({
      ipId: ips.id,
      ipAddress: ips.ipAddress,
      createdAt: ips.createdAt,
      city: ips.city,
      region: ips.region,
      country: ips.country,
      latitude: ips.latitude,
      longitude: ips.longitude,
      timezone: ips.timezone,
      asn: ips.asn,
      isp: ips.isp,
      service: ports.service,
      rawData: ports.rawData,
    })
    .from(ips)
    .leftJoin(ports, eq(ips.id, ports.ipId))
    .where(conditions.length > 0 ? and(...conditions) : undefined)
    .orderBy(orderBy)
    .limit(limit)
    .offset(offset);

  const countQuery = db
    .select({ count: count() })
    .from(ips)
    .leftJoin(ports, eq(ips.id, ports.ipId))
    .where(conditions.length > 0 ? and(...conditions) : undefined);

  const [data, countResult] = await Promise.all([
    dataQuery.execute(),
    countQuery.execute(),
  ]);

  const total = countResult[0]?.count || 0;
  const pageCount = Math.ceil(total / limit);

  return {
    data,
    total,
    pageCount,
  };
}

This search function works as expected, except if an IP has multiple ports, they will yield separate results. When I search for an IP, I want to be able to access the result from the ip table and all the related rows from the ports table all in one object, in one query/transaction. Is this possible? I've been trying to think of an efficient way to do this but I have had no luck so far.

本文标签：