CODEWITHSUNDEEP
postgresqlleft-joindrizzle
Mohsin
Mohsin

Reputation: 137

Grouping postgres left join so it's just an array of the join data

I found this question here

I'm having the same thing but needs a response a bit differently

const data = await db
  .select()
  .from(projects)
  .where(eq(projects.slug, slug))
  .leftJoin(projectPdfs, eq(projects.id, projectPdfs.projectId));

This gives me this data:

[
  {
    projects: { id: 1, name: 'redacted', slug: 'redacted' },
    pdfs: {
      id: 1,
      assetUrl: 'redacted',
      projectId: 1,
      requestTimeMs: '4859.872999999672'
    }
  },
  {
    projects: { id: 1, name: 'redacted', slug: 'redacted' },
    pdfs: {
      id: 2,
      assetUrl: ''redacted',
      projectId: 1,
      requestTimeMs: '4859.872999999672'
    }
  }
]

What I'm looking for is like this:

{
  project: { id: 1, name: 'redacted', slug: 'redacted' },
  pdfs: [
    {
      id: 1,
      assetUrl: 'redacted',
      projectId: 1,
      requestTimeMs: '4859.872999999672'
    },
    {
      id: 2,
      assetUrl: ''redacted',
      projectId: 1,
      requestTimeMs: '4859.872999999672'
    }
  ]
}

I actually tried using groupBy but I didn't get the results I was after.

Upvotes: 2

Views: 312

Answers (1)

gang
gang

Reputation: 1820

You can achieve the desired result using the relations feature in Drizzle ORM, which automatically groups related data into arrays, avoiding the issue of repeated project data for each pdf.

Here’s how you can structure your projects and pdfs tables using Drizzle ORM:

  1. Define your schemas with relations: Drizzle allows you to set up relations so that pdfs are grouped under their respective projects.

import { pgTable, integer, text } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

export const projects = pgTable("projects", {
  id: integer("id").primaryKey(),
  name: text("name"),
  slug: text("slug"),
});

export const pdfs = pgTable("pdfs", {
  id: integer("id").primaryKey(),
  assetUrl: text("assetUrl"),
  projectId: integer("project_id").references(() => projects.id),
  requestTimeMs: text("requestTimeMs"),
});

export const projectRelations = relations(projects, ({ many }) => ({
  pdfs: many(pdfs),
}));

export const pdfRelations = relations(pdfs, ({ one }) => ({
  project: one(projects),
}));
  1. Query the projects with PDFs grouped as an array: With the relations set up, you can now query the projects table and get the associated PDFs grouped into an array under each project.

const result = await db.query.projects.findMany({
  with: {
    pdfs: true,  // PDFs will be grouped as an array under the project
  },
});

Upvotes: 0

Related Questions