No such column Error - Query help
I'm struggling to figure out what I would think is a very basic query in drizzle... I can do it no problem in sqlite:
What I've tried in my backend:
And
Schema to follow in the next message
SELECT ab.user_name, ai.item, ai.trait, ai.guild, ai.rarity, ai.timestamp, ab.bid_amount, ab.use_case FROM auction_items ai
LEFT JOIN (SELECT item_id, user_name, bid_amount, use_case FROM auction_bids WHERE user_id = "test_user_id" AND deleted == 0 GROUP BY item_id) ab
ON ai.id = ab.item_id
WHERE ai.guild = "Y" AND ai.rarity = "P"
SELECT ab.user_name, ai.item, ai.trait, ai.guild, ai.rarity, ai.timestamp, ab.bid_amount, ab.use_case FROM auction_items ai
LEFT JOIN (SELECT item_id, user_name, bid_amount, use_case FROM auction_bids WHERE user_id = "test_user_id" AND deleted == 0 GROUP BY item_id) ab
ON ai.id = ab.item_id
WHERE ai.guild = "Y" AND ai.rarity = "P"
const items = await drizzle.run(sql`SELECT * FROM ${auctionItems} ai
LEFT JOIN (SELECT * FROM ${auctionBids} WHERE ${auctionBids.userId} = ${userId} AND ${auctionBids.deleted} = 0 GROUP BY ${auctionBids.itemId}) ab
ON ${auctionItems.id} = ${auctionBids.itemId}
WHERE ${auctionItems.guild} = ${guild} AND ${auctionItems.rarity} = ${rarity}`);
const items = await drizzle.run(sql`SELECT * FROM ${auctionItems} ai
LEFT JOIN (SELECT * FROM ${auctionBids} WHERE ${auctionBids.userId} = ${userId} AND ${auctionBids.deleted} = 0 GROUP BY ${auctionBids.itemId}) ab
ON ${auctionItems.id} = ${auctionBids.itemId}
WHERE ${auctionItems.guild} = ${guild} AND ${auctionItems.rarity} = ${rarity}`);
const bidSubQuery = drizzle
.select({
itemId: auctionBids.itemId,
username: auctionBids.userName,
bidAmount: auctionBids.bidAmount,
useCase: auctionBids.useCase,
})
.from(auctionBids)
.where(eq(auctionBids.userId, userId))
.groupBy(auctionBids.itemId)
.as("bidSubQuery");
const items = (await drizzle
.select({
item: auctionItems.item,
trait: auctionItems.trait,
guild: auctionItems.guild,
rarity: auctionItems.rarity,
timestamp: auctionItems.expiration,
username: bidSubQuery.username,
bidAmount: bidSubQuery.bidAmount,
useCase: bidSubQuery.useCase,
})
.from(auctionItems)
.leftJoin(bidSubQuery, eq(auctionItems.id, bidSubQuery.itemId))
.where(
and(
eq(auctionItems.guild, guild),
eq(auctionItems.rarity, rarity),
gt(auctionItems.expiration, currentTimestamp),
),
)
.orderBy(auctionItems.expiration)) as AuctionItem[];
const bidSubQuery = drizzle
.select({
itemId: auctionBids.itemId,
username: auctionBids.userName,
bidAmount: auctionBids.bidAmount,
useCase: auctionBids.useCase,
})
.from(auctionBids)
.where(eq(auctionBids.userId, userId))
.groupBy(auctionBids.itemId)
.as("bidSubQuery");
const items = (await drizzle
.select({
item: auctionItems.item,
trait: auctionItems.trait,
guild: auctionItems.guild,
rarity: auctionItems.rarity,
timestamp: auctionItems.expiration,
username: bidSubQuery.username,
bidAmount: bidSubQuery.bidAmount,
useCase: bidSubQuery.useCase,
})
.from(auctionItems)
.leftJoin(bidSubQuery, eq(auctionItems.id, bidSubQuery.itemId))
.where(
and(
eq(auctionItems.guild, guild),
eq(auctionItems.rarity, rarity),
gt(auctionItems.expiration, currentTimestamp),
),
)
.orderBy(auctionItems.expiration)) as AuctionItem[];
3 Replies
Schema
Relations
export const auctionItems = sqliteTable("auction_items", {
id: integer("id").primaryKey({ autoIncrement: true }),
item: text("item", { length: 255 }).notNull(),
trait: text("trait", { length: 255 }).notNull(),
rarity: text("rarity", { length: 255 }).notNull(),
guild: text("guild", { length: 255 }).notNull(),
expiration: text("timestamp")
.notNull()
.default(sql`current_timestamp`),
});
export const auctionBids = sqliteTable(
"auction_bids",
{
id: integer("id").primaryKey({ autoIncrement: true }),
itemId: integer("item_id").notNull(),
userName: text("user_name", { length: 255 }).notNull(),
userId: text("user_id", { length: 255 }).notNull(),
bidAmount: integer("bid_amount").notNull(),
useCase: text("use_case", { length: 255 }).notNull(),
deleted: integer("deleted", { mode: "boolean" }).notNull().default(false),
},
(t) => ({
unq: unique().on(t.itemId, t.userName),
}),
);
export const auctionItems = sqliteTable("auction_items", {
id: integer("id").primaryKey({ autoIncrement: true }),
item: text("item", { length: 255 }).notNull(),
trait: text("trait", { length: 255 }).notNull(),
rarity: text("rarity", { length: 255 }).notNull(),
guild: text("guild", { length: 255 }).notNull(),
expiration: text("timestamp")
.notNull()
.default(sql`current_timestamp`),
});
export const auctionBids = sqliteTable(
"auction_bids",
{
id: integer("id").primaryKey({ autoIncrement: true }),
itemId: integer("item_id").notNull(),
userName: text("user_name", { length: 255 }).notNull(),
userId: text("user_id", { length: 255 }).notNull(),
bidAmount: integer("bid_amount").notNull(),
useCase: text("use_case", { length: 255 }).notNull(),
deleted: integer("deleted", { mode: "boolean" }).notNull().default(false),
},
(t) => ({
unq: unique().on(t.itemId, t.userName),
}),
);
export const auctionBidsRelations = relations(auctionBids, ({ one }) => ({
auctionItem: one(auctionItems, {
fields: [auctionBids.itemId],
references: [auctionItems.id],
}),
}));
export const auctionItemsRelations = relations(auctionItems, ({ many }) => ({
bids: many(auctionBids),
}));
export const auctionBidsRelations = relations(auctionBids, ({ one }) => ({
auctionItem: one(auctionItems, {
fields: [auctionBids.itemId],
references: [auctionItems.id],
}),
}));
export const auctionItemsRelations = relations(auctionItems, ({ many }) => ({
bids: many(auctionBids),
}));
The former (magic sql``) returns this:
The latter, just returns
[]
I don't know how to subqueries in drizzle studio... if anyone can help with that, that'd be awesome 🙂