Drizzle Team•13mo ago

SQLite - one to many relationship, join only returning first match

Hi everyone 🙂 I'm having an issue with my select query using Drizzle + SQLite3. In my DB, a content item can have many content parts. Here's my simplified schema in src/db/schema.ts:

export const contentItems = sqliteTable("content_items", {
  id: integer("id").primaryKey(),
  title: text("title"),
});

export const contentParts = sqliteTable("content_parts", {
  id: integer("id").primaryKey(),
  contentItemId: integer("content_item_id").references(() => contentItems.id),
  title: text("title"),
});

export const contentItems = sqliteTable("content_items", {
  id: integer("id").primaryKey(),
  title: text("title"),
});

export const contentParts = sqliteTable("content_parts", {
  id: integer("id").primaryKey(),
  contentItemId: integer("content_item_id").references(() => contentItems.id),
  title: text("title"),
});

And here's my select. I'm trying to select the content items but join to include the content parts:

import {
  contentItems as contentItemsTable,
  contentParts as contentPartsTable,
} from "@src/db/schema";

export async function getContentItemById(contentItemId: number) {
  const contentItems = await db
    .select()
    .from(contentItemsTable)
    .innerJoin(
      contentPartsTable,
      eq(contentItemsTable.id, contentPartsTable.contentItemId),
    )
    .where(eq(contentItemsTable.id, contentItemId));

  const contentItem = contentItems[0];
  return contentItem;
}

import {
  contentItems as contentItemsTable,
  contentParts as contentPartsTable,
} from "@src/db/schema";

export async function getContentItemById(contentItemId: number) {
  const contentItems = await db
    .select()
    .from(contentItemsTable)
    .innerJoin(
      contentPartsTable,
      eq(contentItemsTable.id, contentPartsTable.contentItemId),
    )
    .where(eq(contentItemsTable.id, contentItemId));

  const contentItem = contentItems[0];
  return contentItem;
}

When I run this select, it only picks up the first matching contentPart:

{
  "content_items": {
    "id": 1,
    "title": "Vision Pro, Spatial Video, and Panoramic Photos",
  },
  "content_parts": {
    "id": 1,
    "contentItemId": 1,
    "title": "Vision Pro, Spatial Video, and Panoramic Photos",
  },
}

{
  "content_items": {
    "id": 1,
    "title": "Vision Pro, Spatial Video, and Panoramic Photos",
  },
  "content_parts": {
    "id": 1,
    "contentItemId": 1,
    "title": "Vision Pro, Spatial Video, and Panoramic Photos",
  },
}

But I can find multiple matching content parts when I run db.select().from(contentPartsTable).where(eq(contentPartsTable.contentItemId, contentItemId)). Is there a way to include the array of all matching 'content_parts' when I run the first select query?

9 Replies

Angelelz•13mo ago

This is the perfect use case for the relational query builder

LarryHudsonOP•13mo ago

Ah ok, so SQL select doesn’t work the way I want it to? And just confirming, the relational query builder works with SQLite and better-sqlite3? I tried adding some relations and couldn’t get it to work, but I might need to spend more time playing with it

Angelelz•13mo ago

I mean, you can make the select do whatever you want So, by default, select will give the results in the same way the you're selecting it In this case, the joined table is getting added to your query in the same rows as the main table But you're only selecting the first row, that's why you're only seeing 1 item of the contant_parts In this line:

const contentItem = contentItems[0];

const contentItem = contentItems[0];

LarryHudsonOP•13mo ago

Ah ok thanks for explaining that, so how would I get one content item, with all its content parts?

Angelelz•13mo ago

If you want you keep using the crud API, here is an example: https://orm.drizzle.team/docs/joins#aggregating-results

Joins [SQL] - Drizzle ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

LarryHudsonOP•13mo ago

Ah ok I see, thanks! I will look more into the relational query builder. Do you know if the query builder is compatible with SQLite and better-sqlite3?

Angelelz•13mo ago

Yes it is BTW, this is not the only option. You could do subqueries and and json aggregation, which basically what the RQB does for you

LarryHudsonOP•13mo ago

Ah ok, thanks for your help here! I might have a play with subqueries. I like the idea of staying a bit closer to SQL After a bit of head scratching I got the query builder working properly this morning! I was missing the part where you add the schema to the main DB export. Thanks again for your help @Angelelz 🙂 I'll copy and paste in my code including the relations, in case anyone runs into the same issue

// src/db/schema.ts
export const contentItems = sqliteTable("content_items", {
  id: integer("id").primaryKey(),
  title: text("title"),
});

export const contentItemRelations = relations(
  contentItems,
  ({ one, many }) => ({
    contentParts: many(contentParts),
  }),
);

export const contentParts = sqliteTable("content_parts", {
  id: integer("id").primaryKey(),
  contentItemId: integer("content_item_id").references(() => contentItems.id),
  title: text("title"),
});

export const contentPartRelations = relations(
  contentParts,
  ({ one, many }) => ({
    contentItem: one(contentItems, {
      fields: [contentParts.contentItemId],
      references: [contentItems.id],
    }),
  }),
);

// src/db/index.ts
import { drizzle } from "drizzle-orm/better-sqlite3";
import * as schema from "./schema";
import type { BetterSQLite3Database } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";

export const sqliteDatabase = new Database("./sqlite.db", {
  verbose: console.log,
});
export const db: BetterSQLite3Database = drizzle(sqliteDatabase, { schema });

// src/content-items/index.ts
import { db } from "@src/db";
import {
  contentItems as contentItemsTable,
} from "@src/db/schema";


export async function getContentItemById(contentItemId: number) {
  const contentItem = await db.query.contentItems.findFirst({
    where: (contentItemsTable, { eq }) => eq(contentItemsTable.id, contentItemId),
    with: {
      contentParts: true,
    },
  });
  return contentItem;
}

// src/db/schema.ts
export const contentItems = sqliteTable("content_items", {
  id: integer("id").primaryKey(),
  title: text("title"),
});

export const contentItemRelations = relations(
  contentItems,
  ({ one, many }) => ({
    contentParts: many(contentParts),
  }),
);

export const contentParts = sqliteTable("content_parts", {
  id: integer("id").primaryKey(),
  contentItemId: integer("content_item_id").references(() => contentItems.id),
  title: text("title"),
});

export const contentPartRelations = relations(
  contentParts,
  ({ one, many }) => ({
    contentItem: one(contentItems, {
      fields: [contentParts.contentItemId],
      references: [contentItems.id],
    }),
  }),
);

// src/db/index.ts
import { drizzle } from "drizzle-orm/better-sqlite3";
import * as schema from "./schema";
import type { BetterSQLite3Database } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";

export const sqliteDatabase = new Database("./sqlite.db", {
  verbose: console.log,
});
export const db: BetterSQLite3Database = drizzle(sqliteDatabase, { schema });

// src/content-items/index.ts
import { db } from "@src/db";
import {
  contentItems as contentItemsTable,
} from "@src/db/schema";


export async function getContentItemById(contentItemId: number) {
  const contentItem = await db.query.contentItems.findFirst({
    where: (contentItemsTable, { eq }) => eq(contentItemsTable.id, contentItemId),
    with: {
      contentParts: true,
    },
  });
  return contentItem;
}

Angelelz•13mo ago

Great! Thank you for the report back

Gaming

Programming

SQLite - one to many relationship, join only returning first match