Drizzle TeamDT
Drizzle Team•3y ago
LarryHudson

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"),
});


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;
}


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",
  },
}


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?
Was this page helpful?