accessing related foreign tables via sql operator

Hi, I have three tables. A url table, a redirect table and a request table. The schema looks like the following,

export const url = pgTable("url", {
  id: uuid("id")
    .primaryKey()
    .default(sql`gen_random_uuid()`),
  user_id: uuid("user_id"),
  full_url: varchar("full_url").notNull(),
  created_at: timestamp("created_at").defaultNow(),
});

export const urlRelations = relations(url, ({ one, many }) => ({
  user: one(user, {
    fields: [url.user_id],
    references: [user.id],
  }),
  redirects: many(redirect),
}));

export const redirect = pgTable("redirect", {
  id: uuid("id")
    .primaryKey()
    .default(sql`gen_random_uuid()`),
  url_id: uuid("url_id"),
  request: uuid("request_id").references(() => request.id, {
    onDelete: "cascade",
  }),
});

export const redirectRelations = relations(redirect, ({ one }) => ({
  url: one(url, {
    fields: [redirect.url_id],
    references: [url.id],
  }),
  request: one(request, {
    fields: [redirect.request],
    references: [request.id],
  }),
}));

export const request = pgTable("request", {
  id: uuid("id")
    .primaryKey()
    .default(sql`gen_random_uuid()`),
  redirect_id: uuid("redirect_id"),
  ip: varchar("ip").notNull(),
});

export const requestRelations = relations(request, ({ one }) => ({
  redirect: one(redirect, {
    fields: [request.redirect_id],
    references: [redirect.id],
  }),
}));

export const url = pgTable("url", {
  id: uuid("id")
    .primaryKey()
    .default(sql`gen_random_uuid()`),
  user_id: uuid("user_id"),
  full_url: varchar("full_url").notNull(),
  created_at: timestamp("created_at").defaultNow(),
});

export const urlRelations = relations(url, ({ one, many }) => ({
  user: one(user, {
    fields: [url.user_id],
    references: [user.id],
  }),
  redirects: many(redirect),
}));

export const redirect = pgTable("redirect", {
  id: uuid("id")
    .primaryKey()
    .default(sql`gen_random_uuid()`),
  url_id: uuid("url_id"),
  request: uuid("request_id").references(() => request.id, {
    onDelete: "cascade",
  }),
});

export const redirectRelations = relations(redirect, ({ one }) => ({
  url: one(url, {
    fields: [redirect.url_id],
    references: [url.id],
  }),
  request: one(request, {
    fields: [redirect.request],
    references: [request.id],
  }),
}));

export const request = pgTable("request", {
  id: uuid("id")
    .primaryKey()
    .default(sql`gen_random_uuid()`),
  redirect_id: uuid("redirect_id"),
  ip: varchar("ip").notNull(),
});

export const requestRelations = relations(request, ({ one }) => ({
  redirect: one(redirect, {
    fields: [request.redirect_id],
    references: [redirect.id],
  }),
}));

Given a url id, I'm trying to access the ip key. My query looks like the following,

const record = await db.query.url.findMany({
      extras: {
        uniqueVisitors: sql<number>`count(distinct(${}))`.as("unique_visitors"),
      },
      with: {
        redirects: {
          with: {
            request: true,
          },
        },
      },
      where: eq(url.id, id),
    });

const record = await db.query.url.findMany({
      extras: {
        uniqueVisitors: sql<number>`count(distinct(${}))`.as("unique_visitors"),
      },
      with: {
        redirects: {
          with: {
            request: true,
          },
        },
      },
      where: eq(url.id, id),
    });

I'm trying to count the unique number of ip addresses in the requst table with the sql operator but when I try to do something like url.request.id. It doesn't exist in url table. Can someone help. : )

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

accessing related foreign tables via sql operator