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:
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).
The raw SQL query is valid and returns the correct, so it's something with mapping this data.
// 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))
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.",
...
![No description](https://cdn.answeroverflow.com/1236433353297887313/image.png)
1 Reply
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),
}),
)