LarryHudson Posts - Answer Overflow

LarryHudson

•Created by LarryHudson on 11/16/2023 in #help

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?

19 replies

Gaming

Programming