Bad Field Error
I have a view that I created that joins two table. here is my implementation :
now when I try to query it with :
it gives me an error of
I tried manually adding all and it still says undefined on the same three columns (variants, images, shop_ids)
export const cartView = mysqlView("carts_and_products").as((qb) => {
return qb
.select({
id: usersCart.id,
user_id: usersCart.user_id,
variantName: usersCart.variant_name,
productName: sql<string>`${products.name.getSQL()}`.as("product_name"),
shopName: sql<string>`${shops.name.getSQL()}`.as("shop_name"),
images: products.images,
variants: products.variants,
quantity: usersCart.quantity,
shopId: products.shop_id,
productId: usersCart.product_id,
})
.from(usersCart)
.innerJoin(products, eq(usersCart.product_id, products.id))
.innerJoin(shops, eq(products.shop_id, shops.id))
})
export const cartView = mysqlView("carts_and_products").as((qb) => {
return qb
.select({
id: usersCart.id,
user_id: usersCart.user_id,
variantName: usersCart.variant_name,
productName: sql<string>`${products.name.getSQL()}`.as("product_name"),
shopName: sql<string>`${shops.name.getSQL()}`.as("shop_name"),
images: products.images,
variants: products.variants,
quantity: usersCart.quantity,
shopId: products.shop_id,
productId: usersCart.product_id,
})
.from(usersCart)
.innerJoin(products, eq(usersCart.product_id, products.id))
.innerJoin(shops, eq(products.shop_id, shops.id))
})
const cartitems = await db
.select()
.from(cartView)
.where(eq(cartView.user_id, Number(userId)) ?? 0)
const cartitems = await db
.select()
.from(cartView)
.where(eq(cartView.user_id, Number(userId)) ?? 0)
⨯ [Error: Unknown column 'undefined' in 'field list'] {
code: 'ER_BAD_FIELD_ERROR',
errno: 1054,
sql: 'select `id`, `variant_name`, `product_name`, `shop_name`, `undefined`, `undefined`, `quantity`, `undefined`, `product_id` from `carts_and_products` where `carts_and_products`.`user_id` = 676424616',
sqlState: '42S22',
sqlMessage: "Unknown column 'undefined' in 'field list'",
digest: '3034184241'
}
⨯ [Error: Unknown column 'undefined' in 'field list'] {
code: 'ER_BAD_FIELD_ERROR',
errno: 1054,
sql: 'select `id`, `variant_name`, `product_name`, `shop_name`, `undefined`, `undefined`, `quantity`, `undefined`, `product_id` from `carts_and_products` where `carts_and_products`.`user_id` = 676424616',
sqlState: '42S22',
sqlMessage: "Unknown column 'undefined' in 'field list'",
digest: '3034184241'
}
1 Reply
My solution was
I still don't know what causes this but it works
export const cartView = mysqlView("carts_and_products").as((qb) => {
return qb
.select({
id: usersCart.id,
user_id: usersCart.user_id,
variantName: usersCart.variant_name,
productName: sql<string>`${products.name.getSQL()}`.as("product_name"),
shopName: sql<string>`${shops.name.getSQL()}`.as("shop_name"),
// images: products.images,
// variants: products.variants,
quantity: usersCart.quantity,
// shopId: products.shop_id,
productId: usersCart.product_id,
images: sql<string[]>`${products.images.getSQL()}`.as("images"),
variants: sql<productVariant[]>`${products.variants.getSQL()}`.as(
"variants",
),
shopId: sql<number>`${products.shop_id.getSQL()}`.as("shop_id"),
})
.from(usersCart)
.innerJoin(products, eq(usersCart.product_id, products.id))
.innerJoin(shops, eq(products.shop_id, shops.id))
})
export const cartView = mysqlView("carts_and_products").as((qb) => {
return qb
.select({
id: usersCart.id,
user_id: usersCart.user_id,
variantName: usersCart.variant_name,
productName: sql<string>`${products.name.getSQL()}`.as("product_name"),
shopName: sql<string>`${shops.name.getSQL()}`.as("shop_name"),
// images: products.images,
// variants: products.variants,
quantity: usersCart.quantity,
// shopId: products.shop_id,
productId: usersCart.product_id,
images: sql<string[]>`${products.images.getSQL()}`.as("images"),
variants: sql<productVariant[]>`${products.variants.getSQL()}`.as(
"variants",
),
shopId: sql<number>`${products.shop_id.getSQL()}`.as("shop_id"),
})
.from(usersCart)
.innerJoin(products, eq(usersCart.product_id, products.id))
.innerJoin(shops, eq(products.shop_id, shops.id))
})