Drizzle Team•8mo ago

Invalid results when joining

I am not sure if something is wrong with my DB or my code but I'm having an extremely weird issue with a relatively simple query. Basically I'm doing a join and except two results A and B, but the fields of those two objects are completely mixed up and totally wrong. Using SQLite with D1. My query and code:

  // find the main page via the website via the product
  const result = await db()
    .select()
    .from(websites)
    .innerJoin(pages, eq(websites.id, pages.websiteId))
    .where(and(eq(websites.productId, productId), eq(pages.url, '/')))
  if (result.length === 0) {
    return new Response('No equivalent page found', { status: 404 })
  }
  console.log('result', JSON.stringify(result, null, 2))

  // find the main page via the website via the product
  const result = await db()
    .select()
    .from(websites)
    .innerJoin(pages, eq(websites.id, pages.websiteId))
    .where(and(eq(websites.productId, productId), eq(pages.url, '/')))
  if (result.length === 0) {
    return new Response('No equivalent page found', { status: 404 })
  }
  console.log('result', JSON.stringify(result, null, 2))

Schema is in the thread due to size constraints. and this logs the following totally mixed up data (id is a short id in the DB and createdAt is a timestamp).

result [
  {
    "website": {
      "id": "zLogFxviLz",
      "createdAt": "2024-05-03 22:01:56",
      "updatedAt": "2024-05-03 22:01:56",
      "url": "/",
      "productId": "OtwjXazjG7"
    },
    "page": {
      "id": "Craftman - Custom ChatGPT chatbot for sales and support",
      "createdAt": "Craftman - Custom ChatGPT chatbot on your website for customer connection | Create bots for sales and artificial intelligence chat bots. ChatGPT website.",
...

result [
  {
    "website": {
      "id": "zLogFxviLz",
      "createdAt": "2024-05-03 22:01:56",
      "updatedAt": "2024-05-03 22:01:56",
      "url": "/",
      "productId": "OtwjXazjG7"
    },
    "page": {
      "id": "Craftman - Custom ChatGPT chatbot for sales and support",
      "createdAt": "Craftman - Custom ChatGPT chatbot on your website for customer connection | Create bots for sales and artificial intelligence chat bots. ChatGPT website.",
...

The raw SQL query is valid and returns the correct, so it's something with mapping this data.

1 Reply

Luca (steebchen)OP•8mo ago

Schema:

const updatedAt = text('updated_at', { mode: 'text' })
  .notNull()
  .default(sql`(current_timestamp)`)
  .$onUpdate(() => new Date().toISOString())

const createdAt = numeric('created_at')
  .default(sql`(current_timestamp)`)
  .notNull()

export const products = sqliteTable('product', {
  id: text('id').primaryKey().notNull(),
  createdAt,
  updatedAt,
  views: integer('views').default(1).notNull(),
  title: text('title').notNull(),
  slug: text('slug').notNull().unique(),
  description: text('description'),
})

export const websites = sqliteTable(
  'website',
  {
    id: text('id').primaryKey().notNull(),
    createdAt,
    updatedAt,
    url: text('url').notNull(),
    productId: text('product_id')
      .notNull()
      .references(() => products.id, {
        onDelete: 'cascade',
        onUpdate: 'cascade',
      }),
  },
  (t) => ({
    productId: index('website__product_id_idx').on(t.productId),
  }),
)

export const pages = sqliteTable(
  'page',
  {
    id: text('id').primaryKey().notNull(),
    createdAt,
    updatedAt,
    title: text('title'),
    url: text('url').notNull(),
    description: text('description'),
    content: text('content'),
    websiteId: text('website_id')
      .notNull()
      .references(() => websites.id, {
        onDelete: 'cascade',
        onUpdate: 'cascade',
      }),
  },
  (t) => ({
    uniqueUrls: index('pages__unique_urls').on(t.id, t.url),
    websiteId: index('pages__website_id_idx').on(t.websiteId),
  }),
)

const updatedAt = text('updated_at', { mode: 'text' })
  .notNull()
  .default(sql`(current_timestamp)`)
  .$onUpdate(() => new Date().toISOString())

const createdAt = numeric('created_at')
  .default(sql`(current_timestamp)`)
  .notNull()

export const products = sqliteTable('product', {
  id: text('id').primaryKey().notNull(),
  createdAt,
  updatedAt,
  views: integer('views').default(1).notNull(),
  title: text('title').notNull(),
  slug: text('slug').notNull().unique(),
  description: text('description'),
})

export const websites = sqliteTable(
  'website',
  {
    id: text('id').primaryKey().notNull(),
    createdAt,
    updatedAt,
    url: text('url').notNull(),
    productId: text('product_id')
      .notNull()
      .references(() => products.id, {
        onDelete: 'cascade',
        onUpdate: 'cascade',
      }),
  },
  (t) => ({
    productId: index('website__product_id_idx').on(t.productId),
  }),
)

export const pages = sqliteTable(
  'page',
  {
    id: text('id').primaryKey().notNull(),
    createdAt,
    updatedAt,
    title: text('title'),
    url: text('url').notNull(),
    description: text('description'),
    content: text('content'),
    websiteId: text('website_id')
      .notNull()
      .references(() => websites.id, {
        onDelete: 'cascade',
        onUpdate: 'cascade',
      }),
  },
  (t) => ({
    uniqueUrls: index('pages__unique_urls').on(t.id, t.url),
    websiteId: index('pages__website_id_idx').on(t.websiteId),
  }),
)

Gaming

Programming

Invalid results when joining